Parse a column between Letters and Numbers

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.

Accepted answers

JosephSerpis

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+).

Regex_11122020.yxmd

Kenda

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.

All comments

JosephSerpis

Regex_11122020.yxmd

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.

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

Parse a Column between letters and Numbers.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703