+ Start a Discussion
Awesome User11Awesome User11 

Need help removing (negative sign) from Currency on Formula Text Field

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, ")","") &

 
Tarun Khatri (TK)Tarun Khatri (TK)
You can convert negative amount to positive and add paranthesis in it.
Use below code in CASE function:
IF(Amount__c < 0, '()'+TEXT(Amount__c*-1), TEXT(Amount__c))


For e.g.: I have updated first CASE value. Try for others as well.
IF(Amount__c < 0, "(","") & "$" & 
CASE(LEN(TEXT(Amount__c)), 
1, IF(Amount__c < 0, '()'+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, ")","") &
-Thanks,
TK

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved.
LBKLBK
You can use ABS() to get rid of - symbol.

Also, I have figured it out that your formula was not handling decimal point gracefully.

Try this formula.
 
IF(Amount__c < 0, "(","") & "$" & 
CASE(LEN(IF(CONTAINS(TEXT(ABS(Amount__c)), "."), LEFT(TEXT(ABS(Amount__c)), FIND(".", TEXT(ABS(Amount__c))) - 1), 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)), LEN(TEXT(ABS(Amount__c))) - 1), 
5, LEFT(TEXT(ABS(Amount__c)), 2) & "," & RIGHT(TEXT(ABS(Amount__c)), LEN(TEXT(ABS(Amount__c))) - 2), 
6, LEFT(TEXT(ABS(Amount__c)), 3) & "," & RIGHT(TEXT(ABS(Amount__c)), LEN(TEXT(ABS(Amount__c))) - 3), 
7, LEFT(TEXT(ABS(Amount__c)), 1) & "," & MID(TEXT(ABS(Amount__c)), 2,3) & "," &  RIGHT(TEXT(ABS(Amount__c)), LEN(TEXT(ABS(Amount__c))) - 4), 
8, LEFT(TEXT(ABS(Amount__c)), 2) & "," & MID(TEXT(ABS(Amount__c)), 3,3) & "," &  RIGHT(TEXT(ABS(Amount__c)), LEN(TEXT(ABS(Amount__c))) - 5), 
9, LEFT(TEXT(ABS(Amount__c)), 3) & "," & MID(TEXT(ABS(Amount__c)), 4,3) & "," &  RIGHT(TEXT(ABS(Amount__c)), LEN(TEXT(ABS(Amount__c))) - 6), 
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)), LEN(TEXT(ABS(Amount__c))) - 7), 
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)), LEN(TEXT(ABS(Amount__c))) - 8), 
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)), LEN(TEXT(ABS(Amount__c))) - 9), null) & 
IF(Amount__c < 0, ")","")
Let me know if this helps.
 
Awesome User11Awesome User11
@LBK  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 User11Awesome User11
TK, I still want the to show as negative.
Any thoughts to above?
LBKLBK
That is surprising.

Are you using the formula I have given above.

I have just tested it with the exact value you have given in the example, it seems to be working without any issues.

Amount__c is a Currency (12,2) type field. Right?
sneha kokasneha koka
Hi, can someone please help me insert a minus sign instead of parenthesis? The field must be displayed as -$185.90. But whenever there is a negative sign, it is displayed in parenthesis. Example: ($185.90) I have enabled multi currency but still no luck.