community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Converting a string of Letters and Numbers into collums?

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

Alteryx Partner

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

 

Highlighted
Alteryx Certified Partner

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

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

 

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!

 

 

 

 

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

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

 

Labels