Search code examples
excelstatecountrycity

How to separate City,State,Country in Excel

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!

Solution

  • 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