Extract last part of email address and add "www." before the @ sig Sandy Crowley 12/31/2008 9:41:01 PM I have searched, tested and walked away. I cannot figure how to: Cell A1 = anyone[ at ]aol.com In cell A2 I'd like to extract "aol.com" and add "www." so the result = www.aol.com I've tried =MID(A1,FIND("[ at ]",SUBSTITUTE(A1,"[ at ]","www.",LEN(A1)-LEN(SUBSTITUTE(A1,"[ at ]","www."))))+1,LEN(A1))) and I know I'm doing it wrong. Thanks for helping! -- Thank you, scrowley(AT)littleonline.com

RE: Extract last part of email address and add "www." before the @ sig Sheeloo 12/31/2008 9:52:00 PM Try ="www." & RIGHT(A1,LEN(A1)-FIND("[ at ]",A1)) assuming there will be an [ at ] symbol in the string You need to find the position for [ at ] and extract LEN minus that number from the right of the string. "Sandy Crowley" wrote: [Quoted Text] > I have searched, tested and walked away. I cannot figure how to: > > Cell A1 = anyone[ at ]aol.com > In cell A2 I'd like to extract "aol.com" and add "www." so the result = > www.aol.com > > I've tried > =MID(A1,FIND("[ at ]",SUBSTITUTE(A1,"[ at ]","www.",LEN(A1)-LEN(SUBSTITUTE(A1,"[ at ]","www."))))+1,LEN(A1))) and I know I'm doing it wrong. > > Thanks for helping! > -- > Thank you, > > scrowley(AT)littleonline.com

RE: Extract last part of email address and add "www." before the @ Sandy Crowley 12/31/2008 9:58:02 PM THANK YOU, THANK YOU, THANK YOU! Works beautifully! You've saved my day. -- Thank you, scrowley(AT)littleonline.com "Sheeloo" wrote: [Quoted Text] > Try > ="www." & RIGHT(A1,LEN(A1)-FIND("[ at ]",A1)) > assuming there will be an [ at ] symbol in the string > > You need to find the position for [ at ] and extract LEN minus that number from > the right of the string. > > "Sandy Crowley" wrote: > > > I have searched, tested and walked away. I cannot figure how to: > > > > Cell A1 = anyone[ at ]aol.com > > In cell A2 I'd like to extract "aol.com" and add "www." so the result = > > www.aol.com > > > > I've tried > > =MID(A1,FIND("[ at ]",SUBSTITUTE(A1,"[ at ]","www.",LEN(A1)-LEN(SUBSTITUTE(A1,"[ at ]","www."))))+1,LEN(A1))) and I know I'm doing it wrong. > > > > Thanks for helping! > > -- > > Thank you, > > > > scrowley(AT)littleonline.com

Re: Extract last part of email address and add "www." before the @ sig "Rick Rothstein" <rick.newsNO.SPAM[ at ]NO.SPAMverizon.net> 12/31/2008 10:08:57 PM Here are three ways to do it... ="www."&MID(A1,FIND("[ at ]",A1)+1,260) =SUBSTITUTE(A1,LEFT(A1,FIND("[ at ]",A1)),"www.") ="www."&TRIM(RIGHT(SUBSTITUTE(A1,"[ at ]",REPT(" ",99)),99)) The first formula is the better of the three formulas (less function calls)... I just thought you might like to try and decipher how the variation methods work. -- Rick (MVP - Excel) "Sandy Crowley" <SandyCrowley[ at ]discussions.microsoft.com> wrote in message news:FCC38D83-DE73-4402-A0AB-22161CE85E3D[ at ]microsoft.com... [Quoted Text] >I have searched, tested and walked away. I cannot figure how to: > > Cell A1 = anyone[ at ]aol.com > In cell A2 I'd like to extract "aol.com" and add "www." so the result = > www.aol.com > > I've tried > =MID(A1,FIND("[ at ]",SUBSTITUTE(A1,"[ at ]","www.",LEN(A1)-LEN(SUBSTITUTE(A1,"[ at ]","www."))))+1,LEN(A1))) > and I know I'm doing it wrong. > > Thanks for helping! > -- > Thank you, > > scrowley(AT)littleonline.com

Re: Extract last part of email address and add "www." before the @ sig Harlan Grove <hrlngrv[ at ]gmail.com> 12/31/2008 10:20:26 PM Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com"> wrote... [Quoted Text] >Try >="www." & RIGHT(A1,LEN(A1)-FIND("[ at ]",A1)) >assuming there will be an [ at ] symbol in the string .... Or use =REPLACE(A1,1,FIND("[ at ]",A1),"www.") which is more efficient since it avoids concatenation.