topic Re: RegEx text data into multiple columns in Alteryx Designer Desktop Discussions

RegEx text data into multiple columns

hloebel — Thu, 13 Jun 2024 21:23:39 GMT

I have multiple rows of text information that isn't comma or tab delimited:

AK                 -1.87 0000013
AL            -361609.85 0000178
AR                -10.23 0000071
AZ           -2633108.01 0000394
CA            -482408.73 0001056

What is the proper expression to split this into 3 columns (state, dollar amount, number of customers)? There is white space/multiple spaces between the state code and the amount, and one space before the number of customers.

Re: RegEx text data into multiple columns

bpatel — Thu, 29 Aug 2019 19:59:56 GMT

Hi @hloebel,

You can use the data cleansing tool to remove the leading and trailing whitespace as well as tabs and duplicate whitespace and then as a text to columns tool. I attached a workflow as well. Hope this helps

Re: RegEx text data into multiple columns

NickSm — Thu, 29 Aug 2019 20:17:09 GMT

@hloebel

Lot's of ways to parse it out. A RegEx expression that would work is:

(\w{2})\s+(\D*\d+\D*\d+)\s(\d+)

Re: RegEx text data into multiple columns

hloebel — Thu, 29 Aug 2019 20:14:43 GMT

What is the difference between the capital and lower case D/d in the expression you have listed?

Re: RegEx text data into multiple columns

NickSm — Thu, 29 Aug 2019 20:16:33 GMT

@hloebel Sure - the \d represents a digit character, where the \D signifies a non-digit. The \D will catch the negative sign and the decimal in your numbers if they're present.

Re: RegEx text data into multiple columns

hloebel — Thu, 29 Aug 2019 20:17:30 GMT

Brilliant! Thanks.