Text String Variable Length

[masonbp2]

I am having an issue extracting data from a column that I have. I need the city of the customer in a new column. The column that I already have is as so:

[TABLE="width: 340"]
<tbody>[TR]
[TD="width: 340"]US315310 - SENSICAL SOLON OH
[TABLE="width: 340"]
<tbody>[TR]
[TD="width: 340"]US318310 - DKM-AD ART BUFFALO NY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
US500190 - STANDARD RUBBER PRODUCTS CO ELKGROVE VILLAGE IL
[TABLE="width: 340"]
<tbody>[TR]
[TD="width: 340"]US525260 - CORTAPE INC CUYAHOGA FALLS OH

I would like to have the results as below

Solon [/TD]
[/TR]
[TR]
[TD="width: 340"]Buffalo [/TD]
[/TR]
</tbody>[/TABLE]
ELkgrove Village
Cuyahoga Falls

Obviously the sole use of left right and mid don't work here as the number of digits is always varying.

 

[Jonmo1]

I don't see any easy way to do it (or difficult way for that matter)
Given the possibility of multi-word Cities.

It would be fairly easy if we could say it's always the 2nd to last word in the string (the state abbreviation is the last).
But with cities of 2 or more words, that's not doable.

Do you have, or can you create a list of either:
All possilble cities
Or
All possible Company Names

 

[masonbp2]

Not unless I try and brute force a list but at that point I might as well just do the columns individually. The issue is further complicated in that some companies have more than one city.
Thanks for taking a look though

 

[Jonmo1]

Do you have any control or input over the source data?
Can a delimiter be added to the source data, like a semicolon after company name, and after the city ?

US315310 - SENSICAL; SOLON; OH[TABLE="class: cms_table, width: 340"]
<tbody>[TR]
[TD="width: 340"]US318310 - DKM-AD ART; BUFFALO; NY[/TD]
[/TR]
</tbody>[/TABLE]

 

[masonbp2]

The data is sourced from IBM Cognos so to the best of my knowledge I don't believe that is an option.

 

[masonbp2]

Jonmo1,

I have figured out a way to attain a list of all possible cities. How would you recommend proceeding?

 

[Jonmo1]

With your list of all possible cities in say G1:G10
And the original string in A1

=LOOKUP(2^15,SEARCH($G$1:$G$10,A1),$G$1:$G$10)

Important, there cannot be any blanks in your list of cities.

 

[masonbp2]

Thank you very much you're a big help!!

 

[Jonmo1]

You're welcome.