You need to sign in to do that

Don't have an account?

Alexis 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

Alexis KasperaviciusThis code has been the "best answer" from Kipp Elkington for over two years with no comments. It not only preserves trailing decimals, it also handles negative numbers in what seems to be the tightest way. Due to the impression that a LOT of peole are using it in production, and comments have stopped, it seems pretty safe that all kinks have been worked out. Also, with the recent relaxation of formula limits in the latest Salesforce releases, the heavy lifting it does to fix this display issue isn't as much of a concern.

Please post here if any issues are encountered.TO USE:In a text formula field, enter the below code and replaceunits__cwith the number field that you need displayed with commas.Note: If this output will be needed in several places, consider creating a custom formula field withjustthis code (e.g. unitsText__c) and use that field where needed to keep things nice & tidy.## All Answers

Shashikant 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 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:

999999999and make it

999,999,999but also make sure that

999doesn't become

,,999?

Thanks much!

A

Jake 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 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:

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

Jake GmerekI 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 Jose

Hi,

Are you sure, this works ??

I tried to have it in my dev org.

Used the code :

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

I had to use the

(MOD(NumberOfEmployees,1000000)in between to get it working fine.Hope its useful

Alexis Kasperavicius

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

Nani44

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.

JonTresko

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:

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 JoseIf your formula gets too complex, you could always use a workflow field update.

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

AmitKumarThanks 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-SFDCAny one had decimal value in their fields? Alexis KasperaviciusI'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.

Joe NagyHey Alex, I appreciate your formula. I refined it a little bit further though:

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 7Hello, What if any number is negative ,then it's working for me?

Alexis KasperaviciusTaking 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")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 KasperaviciusIf you have a currency number, need trailing zeros, and don't care about negative numbers, this works with less code:

Chris WhiffenThanks 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 ElkingtonAlex,

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?

Alexis KasperaviciusHi 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 1Is 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 KasperaviciusDoing this type of number formatting is straightforward in Apex, see below code example:
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)

Gary WI'm looking for a similar solution, but I want the formula to be able to preseve the trailing zero decimals, i.e. display 1337 as "1,337.00". Any advice would be apprciated!

Alexis KasperaviciusThis code has been the "best answer" from Kipp Elkington for over two years with no comments. It not only preserves trailing decimals, it also handles negative numbers in what seems to be the tightest way. Due to the impression that a LOT of peole are using it in production, and comments have stopped, it seems pretty safe that all kinks have been worked out. Also, with the recent relaxation of formula limits in the latest Salesforce releases, the heavy lifting it does to fix this display issue isn't as much of a concern.

Please post here if any issues are encountered.TO USE:In a text formula field, enter the below code and replaceunits__cwith the number field that you need displayed with commas.Note: If this output will be needed in several places, consider creating a custom formula field withjustthis code (e.g. unitsText__c) and use that field where needed to keep things nice & tidy.Nick Cunningham 8I found this solution today and loved it. I also needed it to work the same for negative numbers and adjusted it a bit. So far, t is working well. Thank you!

IF( AND(Unit__c< 1000,Unit__c> -1000) , TEXT( Unit__c),

IF( AND(Unit__c< 1000000,Unit__c>= 1000), TEXT(FLOOR( Unit__c/1000))

& ','

& RIGHT(TEXT( Unit__c), LEN(TEXT( Unit__c)) - LEN(TEXT(FLOOR( Unit__c/1000)))),

IF( AND(Unit__c< 1000000000,Unit__c>= 1000000), TEXT(FLOOR( Unit__c/1000000))

& ','

& MID(TEXT( Unit__c), 1 + LEN(TEXT(FLOOR( Unit__c/1000000))),3)

& ','

& RIGHT(TEXT( Unit__c), LEN(TEXT( Unit__c)) - LEN(TEXT(FLOOR( Unit__c/1000)))),

IF(AND(Unit__c> -1000000,Unit__c<= -1000), TEXT(FLOOR( Unit__c/1000))

& ','

& RIGHT(TEXT( Unit__c), LEN(TEXT( Unit__c)) - LEN(TEXT(FLOOR( Unit__c/1000)))),

IF(AND(Unit__c> -1000000000,Unit__c<= -1000000), TEXT(FLOOR( Unit__c/1000000))

& ','

& MID(TEXT( Unit__c), 1 + LEN(TEXT(FLOOR( Unit__c/1000000))),3)

& ','

& RIGHT(TEXT( Unit__c), LEN(TEXT( Unit__c)) - LEN(TEXT(FLOOR( Unit__c/1000)))),

IF( OR(Unit__c>= 1000000000,Unit__c<= -1000000000), "#TooBig!", NULL))))))

Mathieu KokThe problem I ran into with the proposed solutions is that the compiled character count quickly ran above the limits as I needed multiple values with separators in a single field.

I have rewritten the above (in my case 21,562 characters) to a simpler formula, which not only results in fewer characters (6,191) but is not limited to 3 sets of digits before the final "#TooHigh!" result.

In case you need more sets of digits, it's as simple as adding a line above with the location of the separator:

Alexis KasperaviciusThanks for posting Mathieu! That's a nice way to do it and very clean! I tried it and it works very well with whole, positive numbers, but fields with decimals and/or negative numbers (e.g.

USD -100.32) will throw something like the following:-,100,.32So, depending on the use case it could be perfect! Any chance you (or anyone else) wants to take a crack at handling decimals and negatives with this LEN method? I suppose ROUND could be used somehow, but pennies are often important, so maybe some kind of test for decimals? As or negatives, I have to think about it.

Thanks again for posting as I'm sure it will be used!

Mathieu Kok

I am first invested in adding support for user locale, so I can be sure users see ',' or '.' in the correct use. For my usecase, decimals nor negatives are a thing I need to worry about, but I suppose if you know you always have double decimals (and you know the locale) you could try the following:

Otherwise a FIND function will allow you to calculate string length before decimals to update your LEN accordingly. Same for '-' you can FIND that and ignore that as a first character, or a simple IF on the left character