Happy 8th birthday to the Maveryx Community! Take a walk down memory lane in our birthday blog, and don't miss out on the awesome birthday present that all Maveryx Community members get to take advantage of!
alteryx Community

Alteryx Designer Desktop Discussions

SOLVED

Converting a string of Letters and Numbers into collums?

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.

 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

6 REPLIES 6
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

16 - Nebula

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

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:

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!

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 .

Hope this helps.

Yaseen

5 - Atom

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

Labels