Changing Unique Text within Cell

[digitap]

Hi All,

First posting so take it easy on me

I would like to change 100 cells within my spreadsheet with a formula. So the below will change from anthony@test.com to info@test.com

So all these;

test@test.com
me@test.com
blah@future.com
excel@excel.com

I want to take the characters before the @ sign and changed them all to:

info@test.com
info@test.com
info@future.com
info@excel.com

Any help would be much appreciated.

Thanks
Anthony

 

[dhn46]

You try with fomula

="info"&RIGHT(A1,LEN(A1)-FIND("@",A1)+1)

 

[digitap]

Thank You

Im going to sound like a right lemon now but how do i do that for mass change ??

 

[vds1]

If you have data beginning with A2 then in B2 enter this formula, then drag down

=SUBSTITUTE(A2,LEFT(A2,FIND("@",A2)),"Info@")

where A2 = test@test.com

 

[vds1]

Got me thinking ,

Another alternative , using just two function

=REPLACE(A2,1,SEARCH("@",A2),"Info@")

 

[Scott Huish]

=REPLACE(A1,1,FIND("@",A1),"info@")

 

[digitap]

Hi,

Im not a excel person so need talking through it really. Do i create a new empty column next to all the email addresses and paste that formula into it ?

 

[vds1]

Hi,

Im not a excel person so need talking through it really. Do i create a new empty column next to all the email addresses and paste that formula into it ?

Yes. Example shown above assumes you have data in column A.

 

[digitap]

Ok it looks like its trying to do it but the format is staying the same as =REPLACE(A1,1,FIND("@",A1),"info@") when i hit enter it stays like that instead of changing to info@

 

[vds1]

In Excel 2007 /2010

1)select the column (which contains formula) and right click > format > choose general

2)Make sure under formula tab > calculation options > Automatic is checked.