Help

Re: Question re using Substitute

Solved
Jump to Solution
706 0
cancel
Showing results for 
Search instead for 
Did you mean: 
MettaSong
6 - Interface Innovator
6 - Interface Innovator
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.
2 Solutions

Accepted Solutions

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 

See Solution in Thread

TheTimeSavingCo
17 - Neptune
17 - Neptune

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

IF(
  Amount < 0,
  ABS(Amount)
)

Screenshot 2023-03-17 at 2.59.10 PM.png

See Solution in Thread

3 Replies 3

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
17 - Neptune
17 - Neptune

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

IF(
  Amount < 0,
  ABS(Amount)
)

Screenshot 2023-03-17 at 2.59.10 PM.png

MettaSong
6 - Interface Innovator
6 - Interface Innovator

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!