Text to Columns

Question

I hope someone can assist with this question, I am using text to columns to separate State and Country, however, some countries don't have state separated by comma on the column.

LocationSouth AfricaSouth AfricaSouth AfricaSouth AfricaSouth AfricaAZ, United StatesIL, United StatesUnited KingdomUnited Kingdom

This is the result I am getting, but I really wanted the United States to be on the same column as other countries.

South Africa[Null]South Africa[Null]South Africa[Null]South Africa[Null]South Africa[Null]AZUnited StatesILUnited StatesUnited Kingdom[Null]United Kingdom[Null]

Pablo_Borba · Answer

Thanks all, all suggestions worked perfectly

terry10 · Answer

@pab

Non regex solution attached

text to col country.yxmd

ScottLewis · Answer

Somebody with better RegEx than me could probably give you a way to use RegEx Parse to do this in one but here's a way to do it in two.

Using a formula to add a leading comma on the elements that don't have one sets the data up for text to columns to return countries in column 2.

Formula is: if contains([ Location], ",") then [ Location] else ","+[ Location] endif