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:

[TABLE="width: 426"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]likeaboss@boss.com[/TD]
[TD]likeaboss@likeIcare.com[/TD]
[/TR]
[TR]
[TD]likeadude@dude.com[/TD]
[TD]likeadude@likeIcare.com[/TD]
[/TR]
[TR]
[TD]likeabrother@brother.com[/TD]
[TD]likeabrother@likeIcare.com[/TD]
[/TR]
[TR]
[TD]myneighbour.freak@freaky.com[/TD]
[TD]myneighbour.likeIcare.com[/TD]
[/TR]
[TR]
[TD]myniece.icecreamelolly@sweet.com[/TD]
[TD]myniece.icelikeIcare.com[/TD]
[/TR]
</tbody>[/TABLE]

 

[damian_lee]

Here is another formula you can try...

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

so far the result is consistent:

[TABLE="width: 499"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]likeaboss@boss.com[/TD]
[TD]likeaboss@likeIcare.com[/TD]
[/TR]
[TR]
[TD]likeadude@dude.com[/TD]
[TD]likeadude@likeIcare.com[/TD]
[/TR]
[TR]
[TD]likeabrother@brother.com[/TD]
[TD]likeabrother@likeIcare.com[/TD]
[/TR]
[TR]
[TD]myneighbour.freak@freaky.com[/TD]
[TD]myneighbour.freak@likeIcare.com[/TD]
[/TR]
[TR]
[TD]myniece.icecreamelolly@sweet.com[/TD]
[TD]myniece.icecreamelolly@likeIcare.com[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

the result not consistent:

[TABLE="width: 426"]
<tbody>[TR]
[TD]likeaboss@boss.com[/TD]
[TD]likeaboss@likeIcare.com[/TD]
[/TR]
[TR]
[TD]likeadude@dude.com[/TD]
[TD]likeadude@likeIcare.com[/TD]
[/TR]
[TR]
[TD]likeabrother@brother.com[/TD]
[TD]likeabrother@likeIcare.com[/TD]
[/TR]
[TR]
[TD]myneighbour.freak@freaky.com[/TD]
[TD]myneighbour.likeIcare.com[/TD]
[/TR]
[TR]
[TD]myniece.icecreamelolly@sweet.com[/TD]
[TD]myniece.icelikeIcare.com[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 426"]
<tbody>[TR]
[TD]likeaboss@boss.com[/TD]
[TD]likeaboss@likeIcare.com[/TD]
[/TR]
[TR]
[TD]likeadude@dude.com[/TD]
[TD]likeadude@likeIcare.com[/TD]
[/TR]
[TR]
[TD]likeabrother@brother.com[/TD]
[TD]likeabrother@likeIcare.com[/TD]
[/TR]
[TR]
[TD]myneighbour.freak@freaky.com[/TD]
[TD]myneighbour.likeIcare.com[/TD]
[/TR]
[TR]
[TD]myniece.icecreamelolly@sweet.com[/TD]
[TD]myniece.icelikeIcare.com[/TD]
[/TR]
</tbody>[/TABLE]

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.