Regex and ToNumber Help

[elclark]

Hello,

I have a string field that I am trying to convert to a value.

Current Field:

7,103,209.85 USD

-2,346,213.52 USD

Desired Output as a Number (Int)

7,103,209.85

-2,346,213.52

I'm using the following RegEx expression in the Multi-Field Formula

REGEX_REPLACE([_CurrentField_],"(\d,.+)\s(\w+)","$1")

I was expecting it to at least give me the value (7,103,209.85) but as a string (I know it won't give the value as a number b/c it doesn't recognize commas) but it's not even doing that, it's coming back with just 7 or -2. When I use the RegEx tool by itself on just one column and Parse it instead of Replace, it does correctly break it out into two columns 7,103,209.85 and USD so not sure why it won't work with Replace. I know I can use the ToNumber formula but not sure how to incorporate it with the RegEx expression. Any help would be much appreciated.

[phottovy · Accepted solution]

I would recommend converting it to a "Double" data type. Here is a formula I used:

ToNumber( REGEX_Replace([Current Field:], '[,\sUSD]', ''))

[elclark]

That worked. Thank you!