Hello,
I have a single field of data (let's called it Format) which comes in two format:
Format |
USD123456.00 |
200115USD1234567.89 |
Essentially, for the above two formats, I want them to be parsed into three columns:
i.e for USD123456.00 this would be:
And for 200115USD1234567.89
Simply, put there should be three columns that should only be outputted.
Example:
Date | Currency | Value |
USD | 123456.00 | |
200115 | USD | 1234567.89 |
But also worth bearing in mind is that, sometimes a data-set will only have the Format field in 200115USD1234567.89 only, but should be made to be prepared for the possibility of USD123456.00.
I've taken a look at this example: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Parse-name-out-with-2-different-format...
But it appears to only work when outputting into 2 columns?
Thanks for any help!
Solved! Go to Solution.
Hi @R_L ,
You can use the parse method of the regex tool with the following expression:
(\d*)([^\d]*)(.*)
Each group of parenthesis means a group
\d* = digit zero or more times
[^\d]* = 'everything but a digit' zero or more times
.* = anything zero or more times
To learn more about regex, I suggest you https://regex101.com/
Example attached as well.
Best,
Fernando Vizcaino
Thanks @fmvizcaino
I did the following just now, which worked - but your solution is faster!
IF
REGEX_Match([Format], "(\d{6})(\w{3})(.+)") THEN REGEX_Replace([Format], "(\d{6})(\w{3})(.+)", '$1|$2|$3')
elseif
REGEX_Match([Format], "(\d{0})(\w{3})(.+)") THEN REGEX_Replace([Format], "(\d{0})(\w{3})(.+)", '$1|$2|$3')
Else [Format]
ENDIF
Hi @R_L ,
That's great and it seems like you are pretty great in regex already!!
Using your expression, you almost got the most simplified solution there.
You only need to insert one thing and that will consider both possibilities.
(\d{0,6})(\w{3})(.+)
Best,
Fernando Vizcaino