Find & Replace from list

Hi there,

I've attached an example spreadsheet to show more clearly what I am trying to do...

In sheet "strings" column A I have several text strings that contain location names, such as:

puppies in new york
jobs in chicago
real estate in boston
apartments in chicago
cars for sale in new york

In sheet "locations" I have a list of locations:

Boston
Chicago
New York
Washington

I am trying to find a way of searching through the text strings, if one of the locations exists in the string then it is replaced with the text "location". The final output should look like this:

puppies in location
jobs in location
real estate in location
apartments in location
cars for sale in location

Unfortunately I'll need to do this on a far bigger scale than the attached example, therefore I can't just do a basic Excel Find & Replace job. I've tried searching on Google with no luck, I've also tried to use the =SEARCH & REPLACE features but this has only worked when I do one location at a time.

Any help, advice or pointers in the right direction would be greatly appreciated.

Thank you in advance.

ChainsawDR

This code should get you started.

Adjust the ranges as required, and note that it is case specific, so watch your capitalisation!.

Please Login or Register  to view this content.

Thank you Sweep. The macro failed the debugger as it was missing a " but thats sorted now, however I can't get it working...

I've added it, ran it, tried running the macro while selecting the cells in the "Strings" sheet but no joy.

How should it work? Thank you again Sweep.

I've attached "Example2" which shows how I've implemented the Macro (in case it's something I've done wrong).

In your example, the place names in the second sheet are capitalised, by not in the first sheet, therefore, they're not a match.

Hi Sweep,

I've come across a few obstacles... I think I've got over all but one and was hoping you could advise please.

Two of the locations are "Manchester" & "Chester". Because the string chester is contained within manchester it often produces an output like this: "manlocation". This is just one example, quite often a location name can be spelt within a 'normal' search term.

Therefore I think I need to include Regular expressions within the code, so that rather than looking for the string anywhere, it looks like this:

^manchester$|^manchester_|_manchester_|_manchester$

this should ensure the problem detailed above is avoided. Is this possible to do within the code?

Due to another problem I have already added a RegEx module (http://www.tmehta.com/regexp/add_code.htm) so there are are no longer any non-normal characters in the list of strings, and I have also changed all spaces to underscores. The code runs fine, it just needs the regex adding and then its complete.

I did think of sorting the "Location" sheet so that longer location names were searched first (i.e. manchester is replaced before chester) but this still leaves the problem of small 3/4 letter location names appearing within a normal, non-location word.

Thanks again for all your help so far Sweep - no problem if Regex isn't your area.

ChainsawDR

Hi,

modified to change the text only if the searched text is at the start of a cell, or following a space.

Please Login or Register  to view this content.

Apologies, I have little knowledge of Regex