Text position,remove and replace(inex? substitute?)

[damian_lee]

Hi Masters/Sifu,

I have a question where I need to allocate "@" in an email address and I need to remove text after that and replace with something else:

For Examples:
likeaboss@boss.com
likeadude@dude.com
likeabrother@brother.com

I need to index the "@" and remove the text after that and replace with "likeIcare.com". so, it will become:

likeaboss@likeIcare.com
likeadude@likeIcare.com
likeabrother@likeIcare.com

any suggestion for this noob.

regards

 

[Vidar]

If your data starts in A2 then maybe:

=LEFT(A2,LEN(A2)-SEARCH("@",A2)+2)&"likeIcare.com"

 

[Rick Rothstein]

Here is another formula you can try...

=REPLACE(A2,FIND("@",A2),99,"@likeIcare.com")

 

[damian_lee]

If your data starts in A2 then maybe:

=LEFT(A2,LEN(A2)-SEARCH("@",A2)+2)&"likeIcare.com"

the result not consistent:

likeaboss@boss.com	likeaboss@likeIcare.com
likeadude@dude.com	likeadude@likeIcare.com
likeabrother@brother.com	likeabrother@likeIcare.com
myneighbour.freak@freaky.com	myneighbour.likeIcare.com
myniece.icecreamelolly@sweet.com	myniece.icelikeIcare.com

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[damian_lee]

Here is another formula you can try...

=REPLACE(A2,FIND("@",A2),99,"@likeIcare.com")

so far the result is consistent:

likeaboss@boss.com	likeaboss@likeIcare.com
likeadude@dude.com	likeadude@likeIcare.com
likeabrother@brother.com	likeabrother@likeIcare.com
myneighbour.freak@freaky.com	myneighbour.freak@likeIcare.com
myniece.icecreamelolly@sweet.com	myniece.icecreamelolly@likeIcare.com

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

the result not consistent:

likeaboss@boss.com	likeaboss@likeIcare.com
likeadude@dude.com	likeadude@likeIcare.com
likeabrother@brother.com	likeabrother@likeIcare.com
myneighbour.freak@freaky.com	myneighbour.likeIcare.com
myniece.icecreamelolly@sweet.com	myniece.icelikeIcare.com

<tbody>
</tbody>

Vidar posted a slightly incorrect formula... I think this is what he meant to post.

=LEFT(A2,SEARCH("@",A2)+2)&"likeIcare.com"

 

[Vidar]

the result not consistent:

likeaboss@boss.com	likeaboss@likeIcare.com
likeadude@dude.com	likeadude@likeIcare.com
likeabrother@brother.com	likeabrother@likeIcare.com
myneighbour.freak@freaky.com	myneighbour.likeIcare.com
myniece.icecreamelolly@sweet.com	myniece.icelikeIcare.com

<tbody>
</tbody>

Sorry, my mistake. It should look like this.
=LEFT(A2,SEARCH("@",A2)+1)&"likeIcare.com"

Edit: Just noticed that Rick commented this.

 

[István Hirsch]

Or select the column to process, go to Edit/Replace, and replace „@*.” with „@likeIcare.” (without quotes).

 

[Rick Rothstein]

Or select the column to process, go to Edit/Replace, and replace „@*.” with „@likeIcare.” (without quotes).

@damian_lee,

Note that using István's suggestion will physically changes your original (existing) data.

 

[István Hirsch]

Note that using István's suggestion will physically changes your original (existing) data.

Thanks, Rick. @ OP: copy the column to process in another column first if you wish to keep the original.