function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
deepakpdeepakp 

Converting Number in Currency format

<apex:column headerValue="Amount(K)">
<apex:outputLabel dir="LTR" value="${!ROUND(opp.Amount/1000, 2)}" />
</apex:column>

 

 

I have above in my visualforce page. I sometimes see $3E+1 in amount column.

 

Is ther any way to convert text to currency format in visualforce.

 

<apex:column value="{!opp.Amount}"/> works perfectly. But I want to display amount in thousands and in proper currency format (possibly right aligned with always two digits after decimal point).

 

 

 

Thanks,

Deepak

 

 

Best Answer chosen by Admin (Salesforce Developers) 
Ron WildRon Wild

I had to use a rather convoluted formula to get the right number of decimal places to appear to the right of the decimal point (and add one for even dollar amounts):

 

${!IF(CONTAINS(TEXT(item.UnitPrice),"."),RPAD(TEXT(item.UnitPrice),FIND('.',TEXT(item.UnitPrice))+3,'0'),TEXT(item.UnitPrice)+'.000')}

 

Does anyone have a simpler approach?

 

 

Message Edited by Ron Wild on 03-05-2009 09:00 AM

All Answers

JeremyKraybillJeremyKraybill

There are ways to "convert text to currency format" but you don't need to do that here. In your controller code, just assign one of the currency fields on the object you're dealing with to the number you want to display in proper currency format. e.g.

 

for (Opportunity opp : myOpps) { opp.Amount = opp.Amount / 1000; }

 

Then the VF page that is outputting that number will know how to render it in proper currency format.

 

I've found using native SObject fields is by far the easiest way to get formatting (and input fields) to bind and render correctly. In many cases I actually new() up an SObject just to use it for this purpose.

 

HTH

 

Jeremy Kraybill

Austin, TX

 

Ron WildRon Wild

I had to use a rather convoluted formula to get the right number of decimal places to appear to the right of the decimal point (and add one for even dollar amounts):

 

${!IF(CONTAINS(TEXT(item.UnitPrice),"."),RPAD(TEXT(item.UnitPrice),FIND('.',TEXT(item.UnitPrice))+3,'0'),TEXT(item.UnitPrice)+'.000')}

 

Does anyone have a simpler approach?

 

 

Message Edited by Ron Wild on 03-05-2009 09:00 AM
This was selected as the best answer
MParker1MParker1

Along a similar line, I want to make a formula that will combine a currency field with additional text and display both the formatted currency and text in the same field like:

 

YTD Revenue:   "$4,000  (as of mm/dd/yyyy)"

 

Any ideas on how to make a formula that will add the proper commas to my revenue number after it's turned into text?  I can do something like this but I lose the nice currency formatting:

 

Text(YTDCNR__c) + "as of " +  Date

uptime_andrewuptime_andrew

I'm attempting to use this solution, but the smiley-wink has marred the solution.  When embedding the below in my visualforce page, I'm getting an error saying "Missing '", but I don't see the mismatch myself:

 

 

<apex:column value="${!IF( CONTAINS( TEXT( pl.UnitPrice ), '.'), RPAD( TEXT( pl.UnitPrice ), FIND( '.', TEXT( pl.UnitPrice ) )+3, '0' ), TEXT(pl.UnitPrice)+'.000' )}" headervalue="Unit Price" />

 

 

TinderJoshTinderJosh

This code for a Salesforce formula will take a number and return a formatted string representation of the number in American style Currency. It will also handle negatives and denote them using the American accounting style -1234 == (1,234.00)

 

To use, simply replace "Amount" with the field in question.

 

IF(Amount < 0, "(", "") & "$" &
IF(ABS(Amount) >= 1000000, TEXT(FLOOR(ABS(Amount) / 1000000)) & ",", "") &
IF(ABS(Amount) >= 1000, RIGHT(TEXT(FLOOR(ABS(Amount) / 1000)), 3) & ",", "") &
RIGHT(TEXT(FLOOR(ABS(Amount))), 3) & "." &
IF(MOD(ABS(Amount) , 1) * 100 < 10, "0" & TEXT(ROUND(MOD(ABS(Amount) , 1), 2) * 100), TEXT(MIN(ROUND(MOD(ABS(Amount) , 1), 2) * 100, 99))) &
IF(Amount < 0, ")", "")

Bob MoalliBob Moalli
Thanks for this, it's been helpful.  STUCK though, I'm trying to take it up into the Billions.   I've tried this but it's not presenting properly.  Anyone have a suggestion what I've overlooked?   Thanks for any input.   Bob Moalli 



IF(((AMOUNT))  < 0, "(", "") & "$" &
IF(ABS((( AMOUNT )) ) >= 1000000000, TEXT(FLOOR(ABS((( AMOUNT) ) / 1000000000)) & ",", "") &
IF(ABS((( AMOUNT)) ) >= 1000000, TEXT(FLOOR(ABS((AMOUNT) ) / 1000000)) & ",", "") &
IF(ABS((( AMOUNT )) ) >= 1000, RIGHT(TEXT(FLOOR(ABS((( AMOUNT)) ) / 1000)), 3) & ",", "") &
RIGHT(TEXT(FLOOR(ABS((( AMOUNT)) ))), 3) & "." &
IF(MOD(ABS((( AMOUNT)) ) , 1) * 100 < 10, "0" & TEXT(ROUND(MOD(ABS((( AMOUNT )) ) , 1), 2) * 100), TEXT(MIN(ROUND(MOD(ABS((( AMOUNT )) ) , 1), 2) * 100, 99))) &
IF((( AMOUNT )) < 0, ")", "")