Parse a column between Letters and Numbers

[bryanleblanc]

I need to separate data into multiple columns with columns containing only letters and numbers.  Unfortunately the original data doesn't have the same character count for the letters and numbers except the first character is always a letter.  I have the below data in a single column.  

Col 1

P000000ALG

P0000BOOMV

P00000CORP

P0000HRNMT

P00000OGHQ

P000000DEF

I need to create three columns with each column containing only letters or numbers

Col 1             Col 2              Col 3

P                   000000           ALG

P                   0000               BOOMV

P                   00000             CORP

P                   0000               HRNMT

P                   00000             OGHQ

P                   000000           DEF

I think I can use the REGEX tool but not sure how to specific letters versus numbers.  Any help is greatly appreciated.

[JosephSerpis · Accepted solution]

 Hi @bryanleblanc  regex is definitely one way to tackle this challenge. The syntax (\w)(\d+)(\w+) will find the first letter in the string (\w) and then find all the numbers in the string (\d+) and then all the letters remaining in the string (\w+).

[Kenda]

Hey there @bryanleblanc 

I agree, I would use RegEx in this situation. 

I like to use RegEx in the formula tool. In this case, I would create three new fields, as shown below: 

REGEX_Replace([Col 1], "(\u+)(\d+)(\u+)", "$1")

tonumber(REGEX_Replace([Col 1], "(\u+)(\d+)(\u+)", "$2"))

REGEX_Replace([Col 1], "(\u+)(\d+)(\u+)", "$3")

Here is what is going on in these expressions - First, you're using the parenthesis to basically group your field values into 3 different sections. Within the parenthesis, you're telling Alteryx that the first group will be uppercase letters, the second group will contain digits, and the 3rd section will be uppercase letters. Finally, in the last part of the RegEx expressions, you're telling Alteryx which group to keep for that field using the $ then the group number. In the second expression, I made the field numeric.

Hope this helps!

[marcusblackhill]

Hey @bryanleblanc !

Well I think the REGEX purposed by @Kenda  and @JosephSerpis is the better way to you go, but if for some reason you don't want to use REGEX, you can also do with simple formulas and data cleansing.

Hope that help!

[bryanleblanc]

Thank you Joseph, that worked perfectly!  I wasn't sure what value to use to distinguish letters from numbers so I appreciate you providing the explanation of the formula.  Have a great day!

Bryan

[bryanleblanc]

Thank you Kenda for the quick reply.  This partially works but I need all characters from the original value but your formula only returns 1 number in column 2. 

[Kenda · Accepted solution]

Ah @bryanleblanc you're right. That's a slip up on my part. The second field shouldn't be changed to a numeric field otherwise this will happen. 

Also, if you're ever wondering what expressions to use to build your RegEx, use a RegEx tool then click the dropdown arrow next to the Regular Expression box. This will show you what the abbreviations mean in words.

[malteryx1]

Please go through the below solution. I solved this without using regex and if it solves your query please mark it as a solution