Looking for formula to extract state names from free text cell... HELP!

**howardulson** · 10-21-2014, 11:16 AM

I have a spreadsheet with 12,000 rows of data regarding restaurants. From this data I need to create a column that shows what state each of these restaurants is located in. The problem is, the state name is buried in a free text field. Common text for this field will say "Located in shepherd county on route 29A in the state of Alabama. To get there take highway 46 west..."

The text in each of these fields is different, but the state name is always included in there somewhere. Is there any formula I can use to extract the state name. I understand it may require creating a look-up table with all 50 state names, but after that I'm lost...

Please help!

Thanks,
Howard-

**mcmahobt** · 10-21-2014, 12:53 PM

Can you post a sample workbook?

**AlKey** · 10-21-2014, 01:16 PM

This can be accomplished by doing look up of the state names. For this you would need to create a list of states and use formula.

Please see attached file with states names and a formula in B2

**daddylonglegs** · 10-21-2014, 02:30 PM

That's an excellent approach Alkey, but you need to be careful with states like Arkansas, which contains another state name (Kansas), so ideally the longer name needs to appear lower in the list than the shorter (like West Virginia and Virginia), otherwise you'll never get Arkansas returned......or if the state names will always have Upper case first letters you could use FIND instead of SEARCH