Create formula to sniff out name of city in address column

[Ciresw]

Hello Everyone, I’m a non-technical person, who is new to this forum, so I hope you will excuse my lack knowledge as I explain my problem.

In Column G of my spreadsheet I have address information similar to below:
JOE SMITH
123 PINE AVE.
SACRAMENTO, CA. 95821

The information in Column G looks like it was formatted by someone else in order to easily copy and easily paste into mailing labels. However, because of the way in which the address information is formatted, I cannot sort this sheet by city, and since this sheet has tens of thousands of rows, it is not feasible to do it by hand.

I have had limited success in trying to create a formula which will sniff out the city name and display it another column, and below is an example of my partial success:
=TEXTJOIN(",",1, IF(ISNUMBER(SEARCH(“SACRAMENTO”,G2)),“SACRAMENTO”,"*"))

The above formula displays “SACRAMENTO” in the field where the formula resides, but it would not be practical to use a separate formula for each city, due to the fact that there are approximately 132 individual cities listed within Column G.

I tried created a separate area within the spreadsheet which lists the name of the cities, and then using that range as a reference within the above formula, but for some reason I get an error message and it does not work. Below is an example of what I tried:
=TEXTJOIN(",",1, IF(ISNUMBER(SEARCH($o$2:$o$132,G2)),“True”,“False”))

Of course Column O is where my list of cities is located, and I was just trying to get by with true or false until I could get the first part of the formula working, but thus far, no luck.

Any information greatly appreciated.

[ocorlett]

Is all the info provided in a single cell in Column G, or is it split up into rows each containing one line?

It might be helpful if you would provide the spreadsheet (unless you can’t because of privacy concerns?)

[Ciresw]

All of the address information for each record is contained within Column G. So in other words, Column G contains the address exactly as it would appear on a mailer sent to their house. Unfortunately, the address information came from clients visiting a non-profit agency, so I cannot share their users private data online.

[dscheikey]

Please try the following formula:

=IF(SUMPRODUCT(ISNUMBER(MATCH("*"&O$2:O$4&"*",G2,0))),INDEX(O$2:O$4,MATCH(1,ISNUMBER(MATCH("*"&O$2:O$4&"*",G2,0)),0)),"*")

Wildcards must be switched on!

It outputs the name from the cities list found in the cell.

Ciresw.ods (12,3 KB)

[Ciresw]

Wow! I’m just blown away! That worked so perfectly. There was one minor problem with it confusing the city of West Sacramento with the city of Sacramento, but there are so few of those examples I don’t mind fixing it manually.

I tried to include an example of my final output in case anyone else runs into a similar problem, but not sure if I have been a member long enough to upload attachments.

Ciresw_b.ods (28.8 KB)

[Ciresw]

Thanks again.

[Ciresw]

Got it. Thank you very much. I could have never done that on my own.