Question re using Substitute

Question

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.

Best answer by Dimitris_Goudis

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

Dimitris_Goudis · Accepted Answer

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 · Answer

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

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded