Extracting City and State from a cell without commas

[Penguinducky]

Trying to extract the City and State from a cell that has the Name of the Company, City and State in it and I only want to show the location. Problem scenarios I'm having:
City and State are mushed together (example: MissssaugaOntario)
Company name and city have a / or - between them (example: Company/Mississauga Ontario)

What I need example:
Name of Company Mississauga Ontario > Just want to see Mississauga Ontario

I have thousands of lines like this and will take forever to extract city and state manually. I found a formula that worked for some, but only if it has a comma that separates name of company and city and state. =TRIM(LEFT(RIGHT(SUBSTITUTE(A1,",",REPT(" ",255)),255,255))
No idea what the 255 means but like I said it worked from some lines.

 

[James006]

Hello,

You can only build a solution if there are specific reliable rules :

1. Does each State consist of ONE Word ALWAYS preceded by a blank ?

2. Is each City also represented by ONE Word ALWAYS preceded by a blank ?

 

[Rick Rothstein]

City and State are mushed together (example: MissssaugaOntario)
Company name and city have a / or - between them (example: Company/Mississauga Ontario)

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.

 

[Fluff]

Company name and city have a / or - between them (example: Company/Mississauga Ontario)

If this is always the case, you could use
=MID(A2,FIND("/",SUBSTITUTE(A2,"-","/"))+1,500)

 

[Rick Rothstein]

If this is always the case, you could use
=MID(A2,FIND("/",SUBSTITUTE(A2,"-","/"))+1,500)

Or, alternately, this...

=REPLACE(A1,1,FIND("/",SUBSTITUTE(A1,"-","/")),"")

However, the OP made a specific point about the "mushed" together city and state so that I think the example you are working off of is wrong... I do not think that space is actually there... which means we need to know about multi-word city or state names and how they are constructed, hence my questions.

 

[Fluff]

I do not think that space is actually there

It's not looking for a space, It's simply pulling everything after the / or -.
If the op wants to split the data further (or add spaces if needed) then as you've said we'll need to wait for the OP to answer your & James' question.
Hopefully along with examples of the data.

 

[Rick Rothstein]

It's not looking for a space, It's simply pulling everything after the / or -.
If the op wants to split the data further (or add spaces if needed) then as you've said we'll need to wait for the OP to answer your & James' question.
Hopefully along with examples of the data.

I may have read too much into this example that the OP posted...

What I need example:
Name of Company Mississauga Ontario > Just want to see Mississauga Ontario

I thought he was just being sloppy when he omitted the dash or slash and did not "mush" together the city/state, but he did bold and underline the output he was after which I assumed meant adding the space between the supposedly "mushed" together city/state. Hopefully the OP comes back soon and clarifies everything for us.

 

[Fluff]

Having re-read the op I think you're right & I have a feeling the the / or - is only in some cells but not all.

 

[Penguinducky]

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
[TABLE="width: 466"]
<tbody>[TR]
[TD="width: 466"]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. [/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello,

Confusion ... is the right word ...

You might take a look at fuzzy logic ...

https://www.microsoft.com/en-us/download/details.aspx?id=15011

Hope this will help