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.