RegEx text data into multiple columns

[hloebel]

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.

[bpatel · Accepted solution]

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

[NickSm · Accepted solution]

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

[hloebel]

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

[NickSm · Accepted solution]

@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.

[hloebel]

Brilliant! Thanks.