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] |
@Pablo_Borba
please find the workflow attached
mark done if solved.
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
Thanks all, all suggestions worked perfectly