How to separate City,State,Country in Excel
<p>I found other things online regarding this situation, but it didn't really apply to mine as it is formatted different.</p>

<p>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. </p>

<p>Examples:</p>

<pre><code>Sunnyvale,CA,USA
Toronto,Ontario,Canada
IL,USA
Japan
</code></pre>

<p>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.</p>

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

<p>G10 is the Location column</p>

<pre><code>=LEFT(G10,LEN(G10)-LEN(L10)-LEN(M10)-1) 
</code></pre>

<p>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.</p>

<p>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!</p>

<p>Any help is GREATLY appreciated!</p>