Extracting City and State from a cell without commas

Penguinducky

New Member
Joined
Aug 25, 2016
Messages
3
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.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,926
Office Version
2010
Platform
Windows
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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,902
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,926
Office Version
2010
Platform
Windows
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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,902
Office Version
365
Platform
Windows
I do not think that space is actually there
:confused: 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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,926
Office Version
2010
Platform
Windows
:confused: 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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,902
Office Version
365
Platform
Windows
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

New Member
Joined
Aug 25, 2016
Messages
3
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,099,471
Messages
5,468,813
Members
406,611
Latest member
hanman453

This Week's Hot Topics

Top