Converting a string of Letters and Numbers into collums?

Question

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.

COM2000001001406COM2

000001001407COM2000001001411COM2000001001416COM2000002001202

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 Answer

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 Answer

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

grodge13 · Accepted Answer

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 Answer

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 Answer

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