0

I found other things online regarding this situation, but it didn't really apply to mine as it is formatted different.

I have a spreadsheet with a column that containts a combination of, City,State,Country or State,Country and finally just Country. They do not contain spaces between them, only commas.

Examples:

Sunnyvale,CA,USA
Toronto,Ontario,Canada
IL,USA
Japan

This is an auto-populated list from a search that I exported to a spreadsheet. It can contain a few thousand rows. Locations of users.

I am trying to get separate City, State, and Country columns to assist our recruiting team. I have tried;

G10 is the Location column

=LEFT(G10,LEN(G10)-LEN(L10)-LEN(M10)-1)

to get the city, but it puts the entire field as there are no spaces. I got this from another site and that is when I decided to post my question here.

I am thinking about getting the country column by using a list of all countries and if it matches, to put it in the country column. But just in case someone had a better solution, I turned to you all!

Any help is GREATLY appreciated!

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Michael Crane
  • 3
  • 1
  • 8
  • So the list of countries was a bad idea. I now have a copied list that I am testing with sorted by using the Convert Text to Columns settings. – Michael Crane Sep 22 '16 at 18:43
  • Does the data always follow this pattern: `City - State - Country`? I notice in your examples, you have "City, State, Country", "State, Country" and "Country". Is it safe to say that there will *not* be "City, Country"? I'm just thinking, if there are two names in the cell, is it safe to say there's a country and state? Or could it be "city country"? (I hope this question even makes sense)...If there's two commas, we know we have all three. If one comma, we only have State, Country. Is that correct? Or can you have City, Country? – BruceWayne Sep 22 '16 at 20:50

2 Answers2

0

If they're separated by a common delimiter (in your case ,), you can use the built-in feature Text to Columns.

Highlight the data you want to parse. Go to Data tab, then "Text to Columns". Select "Delimited"and click "Next". Choose "Comma" as the delimiter. Then "Next" to choose where you want the parsed data to go.

This should leave you with a cell per word, where you had them separated by commas.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • This does help, but it puts the state into the same column as city since state,Country only has one comma. Thanks though! – Michael Crane Sep 22 '16 at 19:04
  • @MichaelCrane - Without external data giving city names, etc. it's going to be hard for Excel to "know" what's a city/state/country. But that hopefully gets you started. – BruceWayne Sep 22 '16 at 19:35
  • yeah if this was going to be a very very very important excel sheet, then maybe I would designate "Reading" and "Belfast" as cities in another column or random spot... But then i would probably just adjust the entries so in the system from which they are exported, to show a City column and State column. Rather than all these formulas. :) – Michael Crane Sep 22 '16 at 20:00
0

Three formulas:

City:

=IF(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))=2,LEFT(A2,FIND(",",A2)-1),"")

State:

=IF(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))=1,LEFT(A2,FIND(",",A2)-1),IF(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))=2,LEFT(SUBSTITUTE(A2,B2&",",""),FIND(",",SUBSTITUTE(A2,B2&",",""))-1),""))

Country:

=IF(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))=0,A2,IF(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))=1,SUBSTITUTE(A2,C2&",",""),IF(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))=2,SUBSTITUTE(A2,B2&","&C2&",",""),"")))

this only takes into account your three cases:

  • City,State,Country
  • State,Country
  • Country

It will not work for other combinations:

  • City,Country
  • City,State
  • ...

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • This is awesome! Thank you so much! One question though, I should have mentioned it. There are instances of city,state outside of the US. for example Reading,UK and Belfast,NorthernIreland. These are the only two cases that will be like this. Since they are actual office location. in this case, belfast would be the city and NorthernIreland the country. – Michael Crane Sep 22 '16 at 19:09
  • 1
    Reading,UK is not city,state it is city,Country. And in that case it will need human intervention. Excel is not an AI, it cannot reason. As I stated it will only do the three combinations. Outside of that you will need to tell excel what is a state and what is a country some how. There just is not enough logic manipulation possible to do this as a formula. – Scott Craner Sep 22 '16 at 19:12
  • Good enough for me! Thank you so much Scott! – Michael Crane Sep 22 '16 at 19:17