Your example in line two above does not match what you said in the example in line one...

Is there a space between the city and state or not?

Also, to James' point... if the city or state has two (or more words) in them, is there a space between them or are they "mushed" together with each word starting with an upper case letter followed by lower case letters?

It would help if you could copy/paste actual fully representative examples of the text you have to deal with.

Sorry for the confusion, I've been playing around with a couple different formulas. It sounds like I will have to plug in separate formulas for each type of scenario since there isn't a way to catch everything in one simple formula. I will list examples of the data, formula used and the end result:

Cell A1: Co Maplewood NJ

Cell A2: Co., Boston MA

Cell A3: Co,Freeport IL

Cell A4: Co Burr Ridge IL

Cell A5: Co MississaugaOntario

Cell A6: Co/San Diego CA

Cell A1: Co Maplewood NJ

Instance: Cell contains only spaces, no periods, commas, dashes, etc....

Want it to show: Maplewood NJ

Formula used: =TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",250)),500),500))

Result: Works

Cell A2: Co., Boston MA

Instance: Cell contains comma followed by a space

Want it to show: Boston MA

Formulas used: same one from above - worked.

Also tried =TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",255)),255,255))

Result: Both formulas worked

Cell A3: Co,Freeport IL

Instance: Cell contains no spaces between the comma to separate company and city.

Want it to show: Freeport IL

Formula used: =TRIM(MID(SUBSTITUTE(A3,",",REPT(" ",255)),255,255))

Result: Works

Cell A4: Co Burr Ridge IL

Instance: Cell contains just spaces

Want it to show: Burr Ridge IL

Formula used: Note similar to formula used for A1 except I changed it from 250 to 175. (No clue what these numbers signify??)
=TRIM(LEFT(RIGHT(SUBSTITUTE(A4," ",REPT(" ",175)),500),500))
Cell A5: Co MississaugaOntario
Instance: Cell does not contain a space between City and Province
Want it to show: Mississauga Ontario
Formula used: tried the 3 different formulas above
Result: None of them worked, returned either a blank cell or the original description. |

<tbody>

</tbody>