Splitting a Sentence by just adding a punctuation in the same field

[ben_d_jacob]

I have this entire column where the list is of a location in the format -  Bombay IN. I'd like to change it to Bombay, IN.

is there any way to do this?

Your help would be great. I'm just a beginner so, in case this sounds stupid...Please do forgive.

[PhilipMannering · Accepted solution]

Hi @ben_d_jacob 

Here is a solution,

[BrandonB · Accepted solution]

Could you do a formula that says

Replace([Column Name], " ", ", ")

That basically replaces a space with a comma and a space. This is only a good solution if your first word is only one word, however. 

[atcodedog05 · Accepted solution]

Hi @ben_d_jacob 

You can try something like this

REGEX_Replace([Text], "(.*)\s(\l{2})", "$1, $2")

Output:

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[atcodedog05]

Happy to help 🙂 @ben_d_jacob 

Cheers and Happy Analyzing 😀

Feel free to reach out if you face any issues 🙂

[ben_d_jacob]

Hi, this does help but I'm facing an issue that my data does not have a particular pattern....

Some cells contain - Bombay IN

                                  Bombay City IN

                                  Bombay City office IN

                                  Bombay Rural IN

These are off course just examples but the column does not have a particular pattern.

I don't think there would be a solution to that..but thanks ..all the three replies actually work if they were in one pattern. 🙂

[atcodedog05]

Hi @ben_d_jacob 

Mine works for this scenario too.

Provided last 2 letter are country short form.

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[PhilipMannering]

Is it always IN at the end?

This could work for you,

[ben_d_jacob]

Hi @PhilipMannering 

No, the data is not consistent with IN...There are IN, NY,NJ, IA and many more

[atcodedog05 · Accepted solution]

Hi @ben_d_jacob 

Check mine it will work as long as country code is in 2 characters. Which is in your case.

Output:

Hope this helps🙂