Trying to seperate messy raw data, street, city, state, zip

[KraXed112]

Hello, Just wanna say this forum has been a great help so far in sorting out and cleaning out a raw data dump i have from outlook.

But after cleaning it up for a few days. I feel as if there much be a much simpler way to do this.

My immediate issue is I have address that are all in one column

1b: 4252 Pickford Way, Culver City, CA, 91754
2b: 85191 S 79th St Westchester CA, 90045
3b: 90210 Fiji Way Unit D Marina Del Rey, CA 90292

In alot of cases there are no commas seperating them.

Is it possible to put in a formula in 1 cell stating
If b1 matches a city in a table i've made with all city names to insert that city into that cell?

if b1="Culver City"OR"Westchester"OR"Marina Del Rey" insert matching city into C1?

I'm dealing with roughly 12 cities total.

All i've done so far which is really messy is make a new column with this =IF(ISNUMBER(SEARCH("*Westchester*",B1)),"Westchester","")
and a new column for each city

Sorry if i'm making this more complicated then it needs to be. Spent the entire day browsing excel forums in looking for a easy way to do this.

Thanks ahead for helping.

[Marcol]

Try this workbook

Add "Marina Del Rey" to Column G, (G4)

"Cities" is a dynamic named range, add as many cities as you need, the formula will adjust to suit. the list must be continuous i.e. no blanks.

The formula in C1 is an array formula

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just Enter
Drag/Fill Down.

[KraXed112]

OMG Thank you sooooo much.. Thank you thank you thank you

You have have just taken a huge load off my shoulders thank you.

The next part i have to work on is alot worse unfortunately . I'll be back thank you Marcol!!!