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.
Solved! Go to 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
Lot's of ways to parse it out. A RegEx expression that would work is:
(\w{2})\s+(\D*\d+\D*\d+)\s(\d+)
What is the difference between the capital and lower case D/d in the expression you have listed?
@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.
Brilliant! Thanks.