Hi,
Just want to ask what tool can i use to parse the below data into 2 new columns (Amount & Currency) without omitting the negative sign. I tried to use RegEx with Output Method - Tokenize but the value omits the negative sign.
Thanks,
Rob
Solved! Go to Solution.
Hi @rdelmund
You can use the following statement in parse mode...
(-?\d+.\d+)(.+)
The question mark indicates that the minus is optional. We then take all the digits up to the decimal and all the digits after the decimal.
The brackets indicate new columns to create, so we are creating 2 columns, the 2nd of which contains all characters after our last digit.
Sample workflow attached.
Ben
Hi @rdelmund
Regex would be the correct tool. I used (\u+)$ which means uppercase letters at the end of the string
Using a few test rows this seems to work
Best,
Jordan
Thanks it works!
[post deleted]
[post deleted]
... Post by mcrew was copied here to my REAL account .... Mcrew is my "lurker" account.
I'm just now waking up, so please excuse me for being late to the party. While RegEx can solve the challenge, the data that you provided follows a fixed pattern. The right-most three characters are the currency.
Right([Field],3)
That means that the leftmost characters before the last 3 characters are the value.
Left([Field],Length([Field]) - 3)
Even with 3 calculations, this method of parsing the data is faster (more than 3 times faster) than by using RegEx. Beyond being faster (probably not a huge selling point), the explanation of the formula is easier than having to train someone on the use of RegEx.
I do enjoy RegEx. Don't get me wrong. If there was more variability to the data I might readily suggest it.
Cheers,
Mark
Thanks @MarqueeCrew
i just tried what you've shared and it works as well. This is brilliant!
Though just wondering why does the output via formula has red triangle while the output via RegEx doesn't have it? what does it mean?
Thanks,
Rob