1

I have a formula text field contains three fields. One a currency field, a % field, and a date field. The % and Data field are fine. The issue is with the Currency field. I have been able to add in the "$" sign and comma - leaving out the decimals. But I need to include the parathesis for negatives and not a "-" negative sign. How can I do this without "re-creating" my formula???? FYI - It has to be a formula text field....

 IF(Amount__c < 0, "(","") & "$" & 
    CASE(LEN(TEXT(Amount__c)), 
    1, TEXT(Amount__c), 
    2, TEXT(Amount__c), 
    3, TEXT(Amount__c), 
    4, LEFT(TEXT(Amount__c), 1) & "," & RIGHT(TEXT(Amount__c), 3), 
    5, LEFT(TEXT(Amount__c), 2) & "," & RIGHT(TEXT(Amount__c), 3), 
    6, LEFT(TEXT(Amount__c), 3) & "," & RIGHT(TEXT(Amount__c), 3), 
    7, LEFT(TEXT(Amount__c), 1) & "," & MID(TEXT(Amount__c), 2,3) & "," & RIGHT(TEXT(Amount__c), 3), 
    8, LEFT(TEXT(Amount__c), 2) & "," & MID(TEXT(Amount__c), 3,3) & "," & RIGHT(TEXT(Amount__c), 3), 
    9, LEFT(TEXT(Amount__c), 3) & "," & MID(TEXT(Amount__c), 4,3) & "," & RIGHT(TEXT(Amount__c), 3), 
    10, LEFT(TEXT(Amount__c), 1) & "," & MID(TEXT(Amount__c), 2,3) & "," & MID(TEXT(Amount__c), 5,3) & "," & RIGHT(TEXT(Amount__c), 3), 
    11, LEFT(TEXT(Amount__c), 2) & "," & MID(TEXT(Amount__c), 3,3) & "," & MID(TEXT(Amount__c), 6,3) & "," & RIGHT(TEXT(Amount__c), 3), 
    12, LEFT(TEXT(Amount__c), 3) & "," & MID(TEXT(Amount__c), 4,3) & "," & MID(TEXT(Amount__c), 7,3) & "," & RIGHT(TEXT(Amount__c), 3), null) & 
    IF(Amount__c < 0, ")","") &

Updated with the ABS(

SUBSTITUTE (
IF(Amount__c < 0, "(","") & "$" &
CASE(LEN(TEXT(ABS(Amount__c))),
1, TEXT(ABS(Amount__c)),
2, TEXT(ABS(Amount__c)),
3, TEXT(ABS(Amount__c)),
4, LEFT(TEXT(ABS(Amount__c)), 1) & "," & RIGHT(TEXT(ABS(Amount__c)), 3),
5, LEFT(TEXT(ABS(Amount__c)), 2) & "," & RIGHT(TEXT(ABS(Amount__c)), 3),
6, LEFT(TEXT(ABS(Amount__c)), 3)) & "," & RIGHT(TEXT(ABS(Amount__c)), 3),
7, LEFT(TEXT(ABS(Amount__c)), 1) & "," & MID(TEXT(ABS(Amount__c)), 2,3) & "," & RIGHT(TEXT(ABS(Amount__c)), 3),
8, LEFT(TEXT(ABS(Amount__c)), 2) & "," & MID(TEXT(ABS(Amount__c)), 3,3) & "," & RIGHT(TEXT(ABS(Amount__c)), 3),
9, LEFT(TEXT(ABS(Amount__c)), 3) & "," & MID(TEXT(ABS(Amount__c)), 4,3) & "," & RIGHT(TEXT(ABS(Amount__c)), 3),
10, LEFT(TEXT(ABS(Amount__c)), 1) & "," & MID(TEXT(ABS(Amount__c)), 2,3) & "," & MID(TEXT(ABS(Amount__c)), 5,3) & "," & RIGHT(TEXT(ABS(Amount__c)), 3),
11, LEFT(TEXT(ABS(Amount__c)), 2) & "," & MID(TEXT(ABS(Amount__c)), 3,3) & "," & MID(TEXT(ABS(Amount__c)), 6,3) & "," & RIGHT(TEXT(ABS(Amount__c)), 3),
12, LEFT(TEXT(ABS(Amount__c)), 3) & "," & MID(TEXT(ABS(Amount__c)), 4,3) & "," & MID(TEXT(ABS(Amount__c)), 7,3) & "," & RIGHT(TEXT(ABS(Amount__c)), 3), null) &
IF(Amount__c < 0, ")",""),"-","")
Improve this question
3
  • What does it mean "without "re-creating" my formula??"
    – Santanu Boral
    Apr 19, 2017 at 2:57
  • So do you or don't you have the () currently working. From what you posted it looks like you do. Is all you need is the - removed? Please update your question. If the () are not working state what is going on. Please don't make us guess
    – Eric
    Apr 19, 2017 at 2:59
  • Hello Eric, the () are working but I would like to remove the "-" if possible.
    – Awesome User2
    Apr 19, 2017 at 3:01

1 Answer 1

Reset to default
2

Use Substitute

SUBSTITUTE(
IF(Amount__c < 0, "(","") & "$" & 
    CASE(LEN(TEXT(Amount__c)), 
    1, TEXT(Amount__c), 
    2, TEXT(Amount__c), 
    3, TEXT(Amount__c), 
    4, LEFT(TEXT(Amount__c), 1) & "," & RIGHT(TEXT(Amount__c), 3), 
    5, LEFT(TEXT(Amount__c), 2) & "," & RIGHT(TEXT(Amount__c), 3), 
    6, LEFT(TEXT(Amount__c), 3) & "," & RIGHT(TEXT(Amount__c), 3), 
    7, LEFT(TEXT(Amount__c), 1) & "," & MID(TEXT(Amount__c), 2,3) & "," & RIGHT(TEXT(Amount__c), 3), 
    8, LEFT(TEXT(Amount__c), 2) & "," & MID(TEXT(Amount__c), 3,3) & "," & RIGHT(TEXT(Amount__c), 3), 
    9, LEFT(TEXT(Amount__c), 3) & "," & MID(TEXT(Amount__c), 4,3) & "," & RIGHT(TEXT(Amount__c), 3), 
    10, LEFT(TEXT(Amount__c), 1) & "," & MID(TEXT(Amount__c), 2,3) & "," & MID(TEXT(Amount__c), 5,3) & "," & RIGHT(TEXT(Amount__c), 3), 
    11, LEFT(TEXT(Amount__c), 2) & "," & MID(TEXT(Amount__c), 3,3) & "," & MID(TEXT(Amount__c), 6,3) & "," & RIGHT(TEXT(Amount__c), 3), 
    12, LEFT(TEXT(Amount__c), 3) & "," & MID(TEXT(Amount__c), 4,3) & "," & MID(TEXT(Amount__c), 7,3) & "," & RIGHT(TEXT(Amount__c), 3), null) & 
    IF(Amount__c < 0, ")","") & //You ended your formula here
, '-','')

SUBSTITUTE(text, old_text, new_text) and replace text with the field or value for which you want to substitute values, old_text with the text you want replaced, and new_text with the text you want to replace the old_text.

Improve this answer
7
  • @ Eric, you are the man!!!!
    – Awesome User2
    Apr 19, 2017 at 3:26
  • Hey Eric, Question - when I get a negative value I am receiving a "," in front of the currency. Example: ($,145,000) and it should be ($145,000). Thoughts?
    – Awesome User2
    Apr 19, 2017 at 20:57
  • Try changing Using `ABS(Amount__c) in your case options.
    – Eric
    Apr 19, 2017 at 21:02
  • When I add in ABS in place of the TEXT I receive errors asking to the LEN to be removed and to remove the SUBTRACT( portion as well
    – Awesome User2
    Apr 19, 2017 at 23:50
  • @AwesomeUser2 - I did not say replace the TEXT I said use ABS(Amount__c) in place of Amount__c
    – Eric
    Apr 19, 2017 at 23:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .