REGEX_Replace to remove parentheses around negative numbers and replace with minus sign
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Jonathan
Thanks for the quick response. The below formula works however how do I keep the output to 2 decimal places?
Thanks,
Weyinmi
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
