Question re using Substitute

[MettaSong]

I imported a financial report from PayPal. All of the Negative amounts in the "Amount" field use a "-" in front of the value. I want to create a new field that removes the "-" and just lists the value of the Amount field.

If the amount doesn't have the "-" symbol, I just want it to leave the record blank.

I tried using a substitute formula in the new field, but not sure how to get a blank value in the new field if the original amount value does not have a "-" sign.

SUBSTITUTE(Amount, "-", " ")

What would be the best way to do this? Thanks in advance.

[Dimitris_Goudis · Accepted solution]

Hey @MettaSong, 

Did you try to use the ABS formula for that field. I recommend this because you actually want to have the absolute value from your negative number values. 

This solution will work either your field is number either it is text. 

Yours sincerely, 

Dimitris Goudis 

[TheTimeSavingCo · Accepted solution]

Utilizing Dimitris solution of using ABS, you could put it inside an `IF()` like so:

IF(
  Amount < 0,
  ABS(Amount)
)

[MettaSong]

Thank you @TheTimeSavingCo  & @Dimitris_Goudis - that is an elegant solution. I had never even considered the ABS formula. Perfect! I greatly appreciate your time and wisdom!