How parse data with negative value

[rdelmund]

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

[BenMoss · Accepted 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

[JordanB · Accepted solution]

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

[rdelmund]

Thanks it works!

[mcrew]

[post deleted]

[mcrew]

[post deleted]

[MarqueeCrew · Accepted solution]

@rdelmund,

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

[rdelmund]

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

[BenMoss · Accepted solution]

The red mark shows a warning about the contents of the cell, if you hover over it it should tell you the problem.

My guess would be leading or trailing spaces, which can be removed easily using the data cleanse tool.

Ben