Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
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.

14 REPLIES 14
PhilipMannering
16 - Nebula
16 - Nebula

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
22 - Nova
22 - Nova

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
22 - Nova
22 - Nova

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
22 - Nova
22 - Nova

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
16 - Nebula
16 - Nebula

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
22 - Nova
22 - Nova

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