+ Start a Discussion
Alexis KasperaviciusAlexis Kasperavicius 

How to insert comma separators in TEXT numbers?

Hi all,

 

I am trying to make a field which displays this:

 

48 x 180 (8,640)

 

But I'm getting this (no separators):

 

48 x 180 (8640)

 

Here's the code I'm using:

 

TEXT(Lot__r.Unit_Count__c) & " x " & TEXT(Lot__r.kg_unit__c) &" (" & TEXT((Lot__r.Unit_Count__c * Lot__r.kg_unit__c)) & ")"

 

Is there a simple way to insert comma separators in TEXT numbers - or perhaps a better way to do this? I am stumped and think the only way must be to write some combination of RIGHT and TRIM statements - or something else? This must have come up before, right?

 

It's easy in APEX:

 

<apex:outputText value="{0, number, ###,###,###,###}"><apex:param value="{!Quote.Total_Price__c}"/></apex:outputText>

 

...but does not work in formula fields.

 

What am I missing? Thanks much for any help.

 

Alex

 

 

 

Best Answer chosen by Alexis Kasperavicius
Kipp ElkingtonKipp Elkington
Alex,

Thank you for sharing your code, it has been a real time saver. We have been using it to great effect.

Problem:
We recently had a number over 1,000,000 (rare) and noticed that the code wasn't producing the correct result. The number is 9,400,263.21 and the code was returning 9,002,263.21.

Possible Solution:
I combed through it and I think I have identified the culprit lurking in line 13. "1 +" should be removed from before "FLOOR". In the case of the example number, adding 1 to 9 takes the character length from 1 to 2 and causes the start point of the MID function to be knocked to the right by one character.

Question:
Is there a reason for the part of the code that I have just removed?
 
/* Text Thousands Separator under 1 B - preserves decimals, negatives */
/* Under 1 K */
IF( Units__c < 1000, TEXT(Units__c),

/* Under 1 M */
IF(Units__c < 1000000, TEXT(FLOOR(Units__c /1000))
& ','
& RIGHT(TEXT(Units__c), LEN(TEXT(Units__c)) - LEN(TEXT(FLOOR(Units__c /1000)))),

/* Under 1 B */
IF(Units__c < 1000000000, TEXT(FLOOR(Units__c /1000000))
& ','
& MID(TEXT(Units__c), 1 + LEN(TEXT(FLOOR(Units__c /1000000))),3)
& ','
& RIGHT(TEXT(Units__c), LEN(TEXT(Units__c)) - LEN(TEXT(FLOOR(Units__c /1000)))),

/* 1 B & up */
IF(Units__c >= 1000000000, "#TooHigh!", NULL))))

 

All Answers

Shashikant SharmaShashikant Sharma

This way i don't think you can achieve it, Even though if you are sure that you only want "," after the first digit from left then I can give you formula but if your multiplication record will have more ',' signis as multiplication result increases it wont be possible to maintaing by any formula. You may try with two different fields. One text another field  can be number or currency whatever your multiplication result is. Use both on VFP to show your Text 

 

48 x 180 (8,640)

Alexis KasperaviciusAlexis Kasperavicius

Okay, well I don't think any application of mine would ever have more than 999 million as an answer, so what is the cleanest way you can think of to take an output of:

 

999999999 

 

and make it 

 

999,999,999

 

but also make sure that

 

999

 

doesn't become

 

,,999

 

 

?

 

Thanks much!

 

A

 

Jake GmerekJake Gmerek

Here is sample code you can adapt to your formula:

 

if (Lot__r.Unit_Count__c * Lot__r.kg_unit__c < 1000, text(Lot__r.Unit_Count__c * Lot__r.kg_unit__c),

if(Lot__r.Unit_Count__c * Lot__r.kg_unit__c<1000000,text(floor( Lot__r.Unit_Count__c * Lot__r.kg_unit__c/1000))&','&text(right(Lot__r.Unit_Count__c * Lot__r.kg_unit__c, 3)),

if(Lot__r.Unit_Count__c * Lot__r.kg_unit__c<1000000000,text(floor( Lot__r.Unit_Count__c * Lot__r.kg_unit__c/1000000))&','&text(floor( Lot__r.Unit_Count__c * Lot__r.kg_unit__c/1000))&','&text(right(Lot__r.Unit_Count__c * Lot__r.kg_unit__c, 3)),"Error:Number Greater Than 999,999,999)

 

 

You should be able to see a pattern from there.  It is not pretty, but it is the only way that I can see to accomplish what you want.  I did not test it though so you may want to check the () and whatnot.  Good Luck.

 

Alexis KasperaviciusAlexis Kasperavicius

OK, nested IF statements. Interesting! Thanks very much for putting it together. There were a couple of minor tweaks and here is the tested code for anyone else who wants to do this:

 

IF(Lot__r.Unit_Count__c * Lot__r.kg_unit__c < 1000, TEXT(Lot__r.Unit_Count__c * Lot__r.kg_unit__c),
IF(Lot__r.Unit_Count__c * Lot__r.kg_unit__c < 1000000, TEXT(FLOOR(Lot__r.Unit_Count__c * Lot__r.kg_unit__c/1000)) & ',' & RIGHT(TEXT(Lot__r.Unit_Count__c * Lot__r.kg_unit__c), 3),
IF(Lot__r.Unit_Count__c * Lot__r.kg_unit__c < 1000000000, TEXT(FLOOR(Lot__r.Unit_Count__c * Lot__r.kg_unit__c/1000000)) & ',' & TEXT(FLOOR(Lot__r.Unit_Count__c * Lot__r.kg_unit__c/1000)) & ',' & RIGHT(TEXT(Lot__r.Unit_Count__c * Lot__r.kg_unit__c), 3), "Error:Number Greater Than 999,999,999")))

 I suppose the other way would be using LEN to figure this out.

Jake GmerekJake Gmerek

I thought about len, and it would work, but you would have to have a case for each length beteen 4 and 9 inclusive so that would be 6 cases insead of 3.

Jerun JoseJerun Jose

 

Hi,

 

Are you sure, this works ??

 

I tried to have it in my dev org.

 

Used the code :

 

IF(NumberOfEmployees < 1000, TEXT(NumberOfEmployees),
  IF(NumberOfEmployees < 1000000, TEXT(FLOOR(NumberOfEmployees/1000)) & ',' & RIGHT(TEXT(NumberOfEmployees), 3),
    IF(NumberOfEmployees < 1000000000, TEXT(FLOOR(NumberOfEmployees/1000000)) & ',' & TEXT(FLOOR(NumberOfEmployees/1000)) & ',' & RIGHT(TEXT(NumberOfEmployees), 3), "Error:Number Greater Than 999,999,999")
  )
)

 This worked fine for numbers upto 123,456 ..

 

But when I went over this number, I found that the code was missing something.

 

Here is the version that worked for me

 

IF(NumberOfEmployees < 1000, TEXT(NumberOfEmployees),
  IF(NumberOfEmployees < 1000000, TEXT(FLOOR(NumberOfEmployees/1000)) & ',' & RIGHT(TEXT(NumberOfEmployees), 3),
    IF(NumberOfEmployees < 1000000000, TEXT(FLOOR(NumberOfEmployees/1000000)) & ',' & TEXT(FLOOR(MOD(NumberOfEmployees,1000000)/1000)) & ',' & RIGHT(TEXT(NumberOfEmployees), 3), "Error:Number Greater Than 999,999,999")
  )
)

I had to use the (MOD(NumberOfEmployees,1000000) in between to get it working fine.

 

Hope its useful

Alexis KasperaviciusAlexis Kasperavicius

Shame on me for not testing all iterations. You're right. I marked yours as the solution. Thanks much!

 

Nani44Nani44

Hi Lexlex,

 

is there any regex that we can use in our controller. I have number field. I want to show this number field with comma separated on email template.

 

JonTreskoJonTresko

One interesting thing to point out here...

 

I had a similar scenario with passing values to a Google Chart inside an IMAGE Function. Mine broke because there were decimal places in the number, which the client didn't want anyways. If the TEXT conversion results in a number with decimal places, this formula doesn't work. You'd have to use FLOOR inside ALL TEXT(fields) like this:

 

IF(NumberOfEmployees < 1000, TEXT(FLOOR(NumberOfEmployees)),
  IF(NumberOfEmployees < 1000000, TEXT(FLOOR(NumberOfEmployees/1000)) & ',' & RIGHT(TEXT(FLOOR(NumberOfEmployees)), 3),
    IF(NumberOfEmployees < 1000000000, TEXT(FLOOR(NumberOfEmployees/1000000)) & ',' & TEXT(FLOOR(MOD(NumberOfEmployees,1000000)/1000)) & ',' & RIGHT(TEXT(FLOOR(NumberOfEmployees)), 3), "Error:Number Greater Than 999,999,999")
  )
)

 Only problem is, after all this, I was @ 5018 characters. Ugh... how to trim 18 charachters....

 

Hope that helps anyone down the road referencing this post...

Jerun JoseJerun Jose

If your formula gets too complex, you could always use a workflow field update.

JohnOrdovasJohnOrdovas
I could not get the formula to work properly based on the formulas above. I did a slight adaptation and this seems to work ok now. This is my solution:

IF(FIELD_NAME__c < 1000, TEXT(FLOOR(FIELD_NAME__c)),

IF(FIELD_NAME__c < 1000000, TEXT(FLOOR(FIELD_NAME__c/1000)) & ',' & RIGHT(TEXT(FLOOR(FIELD_NAME__c)),
3),

IF(FIELD_NAME__c < 10000000, TEXT(FLOOR(FIELD_NAME__c/1000000)) & ',' &

MID(text(FIELD_NAME__c), 4, 3)&","&



RIGHT(TEXT(FLOOR(FIELD_NAME__c)), 3),



IF(FIELD_NAME__c < 100000000, TEXT(FLOOR(FIELD_NAME__c/1000000)) & ',' &

MID(text(FIELD_NAME__c), 5, 3)&","&



RIGHT(TEXT(FLOOR(FIELD_NAME__c)), 3),





"Error:Number Greater Than 999,999,999") )
AmitKumarAmitKumar
Thanks for the useful formula but it's not working if there is a decimal in the number. We are using it in a Formula field, which doesn't provide Split function else we could have split the original value in 2 parts & could have appended the decimal part after applying above code. 

Any other possible solution to this issue? 
SAHG-SFDCSAHG-SFDC
Any one had decimal value in their fields? 
Alexis KasperaviciusAlexis Kasperavicius
I've been playing with this over the years and have come up with this version using LEN which detects and includes any decimal amounts. Just replace all instances of Quantity__c below with your number field. Please post back here if it works for you, or any issues.
IF(Quantity__c < 1000, TEXT(Quantity__c ), 
IF(Quantity__c < 1000000, TEXT(FLOOR(Quantity__c /1000)) 
& ',' 
& RIGHT(TEXT(Quantity__c), LEN(TEXT(Quantity__c)) - LEN(TEXT(FLOOR(Quantity__c /1000)))), 
IF(Quantity__c < 1000000000, TEXT(FLOOR(Quantity__c /1000000)) 
& ',' 
& TEXT(FLOOR(Quantity__c /1000)) 
& ',' 
& RIGHT(TEXT(Quantity__c), LEN(TEXT(Quantity__c)) - LEN(TEXT(FLOOR(Quantity__c /1000)))), "#Quantity-Too-High!")))

 
Joe NagyJoe Nagy
Hey Alex, I appreciate your formula. I refined it a little bit further though:
IF(Quantity__c<1000,TEXT(Quantity__c),IF(Quantity__c<1000000,TEXT(FLOOR(Quantity__c/1000))&","&RIGHT(Quantity__c,3),IF(Quantity__c<1000000000,TEXT(FLOOR(Quantity__c/1000000))&","&MID(Quantity__c,LEN(FLOOR(Quantity__c/1000000))+1,3)&","&RIGHT(Quantity__c,3),"TOOMUCH")))
You don't need those calculations to figure out the mid and right for the second and third commas, you just need the next 3 digits after the floor value.
 
manan patel 7manan patel 7
Hello,
What if any number is negative ,then it's working for me?
Alexis KasperaviciusAlexis Kasperavicius
Taking into consideration the need to preserve negatives and decimals, this seems to be the best way I've found so far to do it.

There are some other methods which can handle numbers above 1B using CASE, but they don't preserve decimals and push up the compiled size, this one keeps it under 2k. (Still, yikes!)

While you're here, be sure and vote for the idea (https://success.salesforce.com/ideaView?id=0873A0000003UnYQAU) to extend the TEXT function, so in future we can replace the monstrosity below with: TEXT(Number__c, "###,##0.00")
 
/* Text Thousands Separator under 1 B - preserves decimals, negatives */
/* Under 1 K */ 
IF(Number__c < 1000, TEXT(Number__c), 

/* Under 1 M */ 
IF(Number__c < 1000000, TEXT(FLOOR(Number__c /1000)) 
& ',' 
& RIGHT(TEXT(Number__c), LEN(TEXT(Number__c)) - LEN(TEXT(FLOOR(Number__c /1000)))), 

/* Under 1 B */ 
IF(Number__c < 1000000000, TEXT(FLOOR(Number__c /1000000)) 
& ',' 
& MID(TEXT(Number__c), 1 + LEN(TEXT(1 + FLOOR(Number__c /1000000))),3) 
& ',' 
& RIGHT(TEXT(Number__c), LEN(TEXT(Number__c)) - LEN(TEXT(FLOOR(Number__c /1000)))), 

/* 1 B & up */ 
IF(Number__c >= 1000000000, "#TooHigh!", NULL))))

Please post here if you come up with a better way to do this. I still cringe every time I have to pull this out. 

Again, please click here vote up this idea to make this standard functionality!  (https://success.salesforce.com/ideaView?id=0873A0000003UnYQAU)

 
Alexis KasperaviciusAlexis Kasperavicius
If you have a currency number, need trailing zeros, and don't care about negative numbers, this works with less code:
 
IF( 
  Currency__c >= 1000000, 
  TEXT(FLOOR(Currency__c / 1000000)) & ",", 
  "") & 
IF( 
  Currency__c >= 1000, 
  RIGHT(TEXT(FLOOR(Currency__c / 1000)), 3) & ",", 
  "") & 
RIGHT(TEXT(FLOOR(Currency__c)), 3) & "." & 
IF( 
  MOD(Currency__c , 1) * 100 < 10, 
  "0" & TEXT(ROUND(MOD(Currency__c , 1), 2) * 100), 
  TEXT(MIN(ROUND(MOD(Currency__c , 1), 2) * 100, 99)) 
)

 
Chris WhiffenChris Whiffen
Thanks Alex.

Here's a version of Alex's formula above that addresses negative values:

IF(Currency__c<0,IF(
  Currency__c <= 1000000,
  TEXT(FLOOR(Currency__c / 1000000)) & ",",
  "") &
IF(
  Currency__c <= 1000,
  RIGHT(TEXT(FLOOR(Currency__c / 1000)), 3) & ",",
  "") &
RIGHT(TEXT(FLOOR(Currency__c)), 3) & "." &
IF(
  MOD(Currency__c , 1) * 100 < 10,
  "0" & TEXT(ROUND(MOD(Currency__c , 1), 2) * 100),
  TEXT(MIN(ROUND(MOD(Currency__c , 1), 2) * 100, 99))
),IF(
  Currency__c >= 1000000,
  TEXT(FLOOR(Currency__c / 1000000)) & ",",
  "") &
IF(
  Currency__c >= 1000,
  RIGHT(TEXT(FLOOR(Currency__c / 1000)), 3) & ",",
  "") &
RIGHT(TEXT(FLOOR(Currency__c)), 3) & "." &
IF(
  MOD(Currency__c , 1) * 100 < 10,
  "0" & TEXT(ROUND(MOD(Currency__c , 1), 2) * 100),
  TEXT(MIN(ROUND(MOD(Currency__c , 1), 2) * 100, 99))
))
Kipp ElkingtonKipp Elkington
Alex,

Thank you for sharing your code, it has been a real time saver. We have been using it to great effect.

Problem:
We recently had a number over 1,000,000 (rare) and noticed that the code wasn't producing the correct result. The number is 9,400,263.21 and the code was returning 9,002,263.21.

Possible Solution:
I combed through it and I think I have identified the culprit lurking in line 13. "1 +" should be removed from before "FLOOR". In the case of the example number, adding 1 to 9 takes the character length from 1 to 2 and causes the start point of the MID function to be knocked to the right by one character.

Question:
Is there a reason for the part of the code that I have just removed?
 
/* Text Thousands Separator under 1 B - preserves decimals, negatives */
/* Under 1 K */
IF( Units__c < 1000, TEXT(Units__c),

/* Under 1 M */
IF(Units__c < 1000000, TEXT(FLOOR(Units__c /1000))
& ','
& RIGHT(TEXT(Units__c), LEN(TEXT(Units__c)) - LEN(TEXT(FLOOR(Units__c /1000)))),

/* Under 1 B */
IF(Units__c < 1000000000, TEXT(FLOOR(Units__c /1000000))
& ','
& MID(TEXT(Units__c), 1 + LEN(TEXT(FLOOR(Units__c /1000000))),3)
& ','
& RIGHT(TEXT(Units__c), LEN(TEXT(Units__c)) - LEN(TEXT(FLOOR(Units__c /1000)))),

/* 1 B & up */
IF(Units__c >= 1000000000, "#TooHigh!", NULL))))

 
This was selected as the best answer
Alexis KasperaviciusAlexis Kasperavicius
Hi Kipp, I don't recall where that +1 came from or why it's there, but you're right! Thanks very much for posting and I will mark it as best answer!
Alex Netsch 1Alex Netsch 1
Is there a way to do something like this int he Subject line of an apex email? Essentially my subject line is "We just closed {!Account.Name} for {!relatedTo.Amount}" and would like the Amount to come through formatted with commas
Alexis KasperaviciusAlexis Kasperavicius
Doing this type of number formatting is straightforward in Apex, see below code example:
<apex:outputText value="{0, number, ###,###,###,###}"><apex:param value="{!Quote.Total_Price__c}"/></apex:outputText>

For more examples see the Developer guide here: apex:outputText (https://developer.salesforce.com/docs/atlas.en-us.pages.meta/pages/pages_compref_outputText.htm)