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

Converting a string of Letters and Numbers into collums?

Sean_newell
5 - Atom

Hi All,

 

I am new to Alteryx and have no experience using Regx,

 

I am trying to convert an input of 3 letters and 13 numbers;

 

COM2000001001406
COM2000001001407
COM2000001001411
COM2000001001416
COM2000002001202

into 3 columns  of 3Letters and 1 number, 6 numbers and 6 Numbers.

 

COM2000001001406

COM2

000001001407
COM2000001001411
COM2000001001416
COM2000002001202

 

In excel I would do this with text to column by fixed width, but I can't find a similar function within Alteryx.

 

Thanks so much

 

Sean Newell

6 REPLIES 6
OllieClarke
15 - Aurora
15 - Aurora

Hi @Sean_newell you can use the REGEX tool in parse mode with the following REGEX to get your 3 columns:

(\w{3}\d)(\d{6})(\d{6})


You can then drop the initial field with a select tool to get your required output

clipboard_image_1.png

 

JosephSerpis
17 - Castor
17 - Castor

Hi @Sean_newell you can do this without regex tools using the left, substring and right funcations have attached a workflow.

grodge13
7 - Meteor

Hi Sean, 

 

I'm no good at regex either but if your data is always in the same format you could just use the formula tool and create 3 more columns using the following formulas:

 

1) COL_1 = LEFT([ID],4)

2) COL_2 = RIGHT(LEFT([ID],10),6)

3) COL_3 = Right([ID], 6)

 

That would give you the desired output: 

 

Column Split.PNG

 

PeterA1
Alteryx
Alteryx

Hey @Sean_newell Here is an example of how to solve this with Regex:

 

(\w{3}\d{1})(\d{6})(\d{6})

 

the three parts are: 

 

(\w{3}\d{1})  3 letters followed by 1 digit

and then two sets of: 

 

(\d{6})   6 digits

 

Regex is an extremely powerful tool, I had no experience until about two months ago. Here are some great resources to help you learn regular expressions:

 

http://www.rexegg.com/regex-quickstart.html#ref

 

https://regexr.com/

 

 

Hope this helped!

 

 

 

 

yaseenali
5 - Atom

Hi Sean,

 

One Regex solution would be ([a-zA-z]{3}\d)(\d{6})(\d{6})

Each of the round brackets represent one match group corresponding to each column in your requirements. I attached a workflow using some of your sample data to show the configuration of the regex tool as well as the screenshot below. 

Another useful tool in the building and testing of regular expressions is https://regex101.com/r/sR4hA5/1 .

 

clipboard_image_0.png

 

Hope this helps.

 

Yaseen

Sean_newell
5 - Atom

thank you for your the solution, as well as where to learn your Jedi ways 🙂

 

Labels