Find cells that start with a specific text and replace with

[acargo]

Hi all,


I am pretty sure I've been able to use the find and replace function for this before; I have a complete brain freeze.

I have a lot of entries on excel; they are urls. Some of them have the root domain included and some don't. for example:

www.example.com/product123
/product435
/product987
www.example.com/product435

and so on.

I want to find those that have www.example.com and replace them so I only have /product and the number behind (for example /product123). What wildcard can I use to do this please? I've tried several things without success.

Any help on this would be much appreciated.

thanks,

 

[louisH]

Hi,

the SUBSTITUTE function ?

=SUBSTITUTE(YourEntry, "www.example.com", "")

with VBA :

Replace(YourRange, "[URL="http://www.example.com"]www.example.com[/URL]", "")

Or you can just press Ctrl+H in your worksheet and use the replace tool

Hope it helped

 

[acargo]

Hi louisH,

That worked nicely. Thank you

Do you know if there's a way to do it through the find and replace though?

A.

 

[louisH]

Are you looking for a macro to do this ?

One equivalent of the replace tool (Ctrl+H) in VBA is :

    ActiveSheet.UsedRange.Replace What:="[URL="http://www.example.com"]www.example.com[/URL]", Replacement:=""