How to search and extract specific words from an Excel worksheet ?

**newbr33d** · 10-24-2008, 08:33 PM

Let's say i have in an A1...A800 column the names of 800 persons and the cities they live in, like:
A
1 John Connor Chicago
2 Mary Alice, New York
3 Mark New York Smith
4 Washington, David Pitt
5 Salt Lake City Andrew Barrymore

I want to extract, in the B1...B800 column the city they live in, like:

B
1 Chicago
2 New York
3 New York
4 Washington
5 Salt Lake City

Can anyone know how to do that ? The A1...A800 column is very deorganised

, sometimes i have commas after the names, sometimes the city name is in front of the person name, sometimes after or in the middle. Thanks!

**sglife** · 10-24-2008, 09:46 PM

I think It's impossible with the original deorganised data.
You need to manually organise it first.

Then you can use the text functions, eg, Right(), Left(), Mid()

**davesexcel** · 10-24-2008, 10:13 PM

VBA could work for this
Create a list of the cities, the macro will search the list and extract the city from the strings if they are there. Spelling is a factor of course.
Look at the example, enable macros when opening the workbook.
Click the button to extract the cities from the list.

**newbr33d** · 10-25-2008, 06:20 AM

Originally Posted by davesexcel

VBA could work for this
Create a list of the cities, the macro will search the list and extract the city from the strings if they are there. Spelling is a factor of course.
Look at the example, enable macros when opening the workbook.
Click the button to extract the cities from the list.

Really thanks for the code. It works perfectlly.

But, if a city is not found in the cities list i will obtain an empty row. Can i obtain in that row a text like "No city found" with red color. Then i can complete the cities list in case the person is from a city which i don't have in my city list. Also, can i have the cities names (B column) in a specified column (let's say G column, i understand that if i change the offset from 1 to.. n.. i will obtain the value in the n column) ? I want also to obtain in the A column only the person name (or other column), without the name of the city. Thanks again!