Text to Columns

[Pablo_Borba]

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.

Location

South Africa

South Africa

South Africa

South Africa

South Africa

AZ, United States

IL, United States

United Kingdom

United 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]
AZ	United States
IL	United States
United Kingdom	[Null]
United Kingdom	[Null]

[alexnajm]

Doable with a Formula!

[Raj]

@Pablo_Borba 
please find the workflow attached
mark done if solved.

[ScottLewis]

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

 

[terry10]

@pab

Non regex solution attached 

[Pablo_Borba]

Thanks all, all suggestions worked perfectly