How to convert a number to a negative number when the minus sign suffixed?

[Anamika]

Hi,

I have values that has the negative sign as a suffix instead of a pre-fix. E.g. "100 -" instead of "-100". How can I convert this to (100)? Need help with this.

[MarqueeCrew]

Hi @Anamika,

Here's an approach for you to try:

Regex_Replace([Field1],"(\d+)(.*)",'$2,$1')

It will swap things for you.

Cheers,

Mark

[Anamika]

Thanks but i forgot to mention that my number is stored as a string...hence, your earlier solution is not working.

[MarqueeCrew]

What exactly is not working?  It should operate on strings (not numbers).  Did you change [Field1] to your input field?

[Anamika]

Yes i did that. So the "100-" is now showing as ",00-,1"

[MarqueeCrew]

My apologies @Anamika ,

My configuration included a comma.....

Regex_Replace([Field1],"(\d+)(.*)",'$2$1')

All should be better now.

Cheers,

Mark

[DavidP]

How about a simple idea like this?

[DavidP]

Sorry, you'll need to put an ABS in there as well, like this:

if Contains([Field1], '-') then '('+tostring(abs(tonumber([Field1])))+')' else [Field1] endif

[MarqueeCrew · Accepted solution]

@DavidP,

Does that really simplify life?  

If Right([Field1], 1) = '-'
Then '-'+ Trim([Field1],'-')
Else [Field1]
Endif

If you really don't like RegEx.....

Cheers,

Mark

[DavidP]

Didn't mean to cause offence Mark.

The regex does a great job of flipping the minus sign to the front, but the desired outcome is to identify a negative number and display it as (100).