Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

weygu
5 - Atom

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,
6 REPLIES 6
Jonathan-Sherman
15 - Aurora
15 - Aurora

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'),',',''))

 

Jonathan-Sherman_0-1593541521636.png

 

 

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
5 - Atom

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
15 - Aurora
15 - Aurora

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

 

Jonathan-Sherman_1-1593542703064.png

 

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
5 - Atom

Thanks!

Reddy4
7 - Meteor

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

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.

Labels