If a cell contains a specific word then delete this specific word

[Wil3rd]

Hi everyone,

I am looking for help.

Many of my cells are containing: "mailto:xxxxxx@gmail.com"

I would like to delete "mailto:" and keep the email address in all cells.
Something like : If cells bewtwenn B2:B100 are containing "mailto:" then delete it. (But i don't want to delete the cell, i want to delete only "mailto:" and keep the entire email adress which in the same cell.
If anyone have any idea, i am in. By doing a macro or whatever you want.

Thanks in advance !

 

[Candyman8019]

the simplest way would be to use the find/replace feature. Replace "mailto:" with blank.

 

[Candyman8019]

If you have frequent data input and want a formula to do this, then use substitute. For example: =SUBSTITUTE(D3,"Mailto:","")

 

[Wil3rd]

If you have frequent data input and want a formula to do this, then use substitute. For example: =SUBSTITUTE(D3,"Mailto:","")

Thanks a lot for your quick reply, i don't understand why my ecxel is sending a message error. I don't get why and where there is a mistake in my formula. I put excactly same formula as you.

 

[Candyman8019]

What error message are you getting? D3 should be replaced with whatever cell contains your text.

 

[Fluff]

Try changing the commas to semi-colons.

 

[Wil3rd]

If you have frequent data input and want a formula to do this, then use substitute. For example: =SUBSTITUTE(D3,"Mailto:","")

I have been able to make it at the end. But the formula is deleting all of my cell. My cell is "mailto:xxxx@email.com" and in my cell i want to keep the email adress and only delete "mailto:".

 

[Candyman8019]

To be clear, this formula MUST be put into a cell other than the cell that contains your text.

 

[Candyman8019]

Try changing the commas to semi-colons.

May be a learning moment for me ...how would semi-colons differ from commas in a formula?

 

[Fluff]

It's dependant on region, a lot of European countries use a semi-colon as the formula separator.