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.

[Anamika]

Thanks! So this is working on some number and not on others.

I have:

7,497.63-

4,678.98

203.45-

10.98

11.98

105.63-

The result i am getting after using your formula is:

(7)

4,678.98

(203.45)

10.98

11.98

(1)

I wonder why the formula is working on few values.

[Anamika]

Thanks @DavidP  for your help!

[MarqueeCrew]

... because I didn't know about the text formatting....

The text formula of:

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

will work for you.  If you are converting to a number, you'll want:

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

Cheers,

Mark

[Anamika]

@MarqueeCrew  this worked. The regex was splitting but was not changing the position of the "-" 

[MarqueeCrew]

@DavidP ,

No offense taken.  I like alternative approaches.  In this case, with the presence of commas, I actually prefer the text functions.  While RegEx is an expensive text function, I try to avoid some text functions when possible.  Sorry that I came across that way.

Cheers,

Mark