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