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,

Regex

Help

Accepted answers

Jonathan-Sherman

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

20200630 - NegativeNumbers.yxmd

All comments

Jonathan-Sherman

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

20200630 - NegativeNumbers.yxmd

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

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703