ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
1 Day Left! - The Alteryx Community will be temporarily unavailable for a few hours due to implementation of the new SSO experience starting tomorrow at 5pm MDT. Please plan accordingly. For more information, read the blog.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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

ben_d_jacob
7 - Meteor

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
14 - Magnetar
14 - Magnetar

Hi @ben_d_jacob 

 

Here is a solution,

PhilipMannering_0-1606973628766.png

 

BrandonB
Alteryx
Alteryx

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
17 - Castor

Hi @ben_d_jacob 

 

You can try something like this

 

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

 

Output:

atcodedog05_0-1606975408305.png

Workflow:

atcodedog05_1-1606973863154.png

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
17 - Castor

Happy to help 🙂 @ben_d_jacob 

 

Cheers and Happy Analyzing 😀

 

Feel free to reach out if you face any issues 🙂

ben_d_jacob
7 - Meteor

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
17 - Castor

Hi @ben_d_jacob 

 

Mine works for this scenario too.

atcodedog05_0-1606980849863.png

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
14 - Magnetar
14 - Magnetar

Is it always IN at the end?

 

This could work for you,

PhilipMannering_0-1606980919785.png

 

ben_d_jacob
7 - Meteor

Hi @PhilipMannering 

 

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

atcodedog05
17 - Castor

Hi @ben_d_jacob 

 

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

 

Output:

atcodedog05_0-1606983760572.png

Hope this helps🙂

Labels