How would I split text separated by commas into new cells?

M.OConnell

Example, in one cell I have the text "Boston, MA, USA"

How do I split that to have Boston in one column, MA in the second, and USA in the third, all on the same row?

Kelly P.

Let's say that your text "Boston, MA, USA" is in a column called "Location" and that you want to separate the text into three columns called City, State, and Country. The formulas that work for me are as follows:

City: =LEFT(Location@row, FIND(", ", Location@row) - 1)

State: =MID(Location@row, FIND(", ", Location@row) + 1, FIND(", ", Location@row, FIND(", ", Location@row) + 1) - FIND(", ", Location@row) - 1)

Country: =RIGHT(Location@row, LEN(Location@row) - FIND(", ", Location@row, FIND(", ", Location@row) + 1) - 1)

Does this help?

MOC

Thank you Kelly. I will test that. How would you edit that in case the data came across as ['Boston','MA','USA']? I just learned thats likely how it will come across.