Converting a string of Letters and Numbers into collums?

[Sean_newell]

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.

COM2	000001	001406
COM2	000001	001407
COM2	000001	001411
COM2	000001	001416
COM2	000002	001202

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

[OllieClarke · Accepted solution]

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

[JosephSerpis · Accepted solution]

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

[grodge13 · Accepted solution]

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: 

[PeterA1 · Accepted solution]

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 · Accepted solution]

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 .

Hope this helps.

Yaseen

[Sean_newell]

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