REGEX_Replace to remove parentheses around negative numbers and replace with minus sign

[weygu]

Hi,

Any suggestions on how I can adapt my current REGEX_Replace formula to achieve the desired output.

Input: (55,683.00)

Formula: ToNumber(REGEX_Replace([Field_3],"\((.*)\)",'-$1'))

Current Output: -55

Desired Output: -55,683.00

I basically want to keep the number as is but replace the parenthesis with a minus sign at the start.

Note dataset includes both positive and negative numbers.

Thanks,

[weygu]

Thanks,

[Jonathan-Sherman · Accepted solution]

Hi @weygu,

Your Formula works fine, however if you're trying to use this as a numeric data type (e.g. double) you'll want to remove the comma and convert it to a number:

ToNumber(ReplaceChar(REGEX_Replace([Field1], '\((.*)\)', '-$1'),',',''))

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[weygu]

Hi Jonathan

Thanks for the quick response. The below formula works however how do I keep the output to 2 decimal places?

Thanks,
Weyinmi

[Jonathan-Sherman]

Hi @weygu,

A double data type would allow for decimal places, they simply aren't displayed if they aren't needed. Which in your example they weren't.

If you did want to display .00 for example you could change the data type to a Fixed Decimal, where "Scale" is the number of decimal places (i've set to 2).

If this solves your issue please mark the answer as correct, if not let me know! I've attached an updated workflow for you to download if needed.

Regards,

Jonathan

[weygu]

Thanks!

[Reddy4]

What if it has $ sign. How can I sum below amount.

$21.61
$3.05
$11.85
($32.45)
$6.43
$7.57
$11.17

[JoeMolinaro]

how would you do the reverse of that.  That is if you have numbers coming in as a Field Type of Double and the contain the negative number without a comma separator for numbers over a thousand, and I would like the presentation to be bracketed instead of displaying the negative sign.

Input: -1000   Desired output (1,000)

The results go to a reporting tool in a table and I thought I could resolve the conversion there but would also resolve it to the records that are going to the table if I knew a good way.  After converting the field type from a Double to a String I tried this.

if Contains([(Over) Under Budget], "-") then Replace([(Over) Under Budget], "-", "(")+")"
else [(Over) Under Budget] endif

It got me the brackets I was looking for but not the comma separator.