SOLVED
Home

How to add separator (comma) inside a formula?

%3CLINGO-SUB%20id%3D%22lingo-sub-44106%22%20slang%3D%22en-US%22%3EHow%20to%20add%20separator%20(comma)%20inside%20a%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-44106%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20to%20separate%20the%20value%20of%20SUM%20by%20comma%20in%20this%20case%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20234px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F10548iAA275770FB9B2883%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.JPG%22%20title%3D%22Capture.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-44106%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETips%20and%20Tricks%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-242544%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%20separator%20(comma)%20inside%20a%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-242544%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20check%20this%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.ablebits.com%2Foffice-addins-blog%2F2016%2F07%2F07%2Fcustom-excel-number-format%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.ablebits.com%2Foffice-addins-blog%2F2016%2F07%2F07%2Fcustom-excel-number-format%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-242541%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%20separator%20(comma)%20inside%20a%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-242541%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3ESergei%20Baklan%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIf%20i%20want%20to%20use%20decimal%20places%20in%20the%20number%2C%20Formula%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-44403%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%20separator%20(comma)%20inside%20a%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-44403%22%20slang%3D%22en-US%22%3E%3CP%3EActually%20no%20difference%2C%20for%20me%20it%20was%20faster%20enter%20the%20formula%20ans%20copy%2Fpaste%20it%20here.%20In%20general%2C%20for%20whole%20numbers%20you%20may%20use%20something%20like%20TEXT(A1%2C%22%23%2C%23%22).%20Another%20story%20if%20you%20would%20like%20to%20make%20different%20formatting%20for%20positive%20and%20negative%20numbers%2C%20add%20text%20and%20or%20colors%20into%20formatting%2C%20etc%20-%20all%20that%20additional%20symbols%20matter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20particular%2C%20you%20may%20format%20your%20cell%20with%20%3DSUM(D3%3AD12)%20as%26nbsp%3B%22Total%3A%20%22%23%2C%23%23%23%20(enter%20this%20into%20the%20Custom%20format%20of%20the%20cell)%20that%20gives%20the%20same%20result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EComma%20just%20separates%20your%20number%20on%20thousans%2C%20millions%2C%20billions%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMore%20about%20formatting%20is%20here%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FNumber-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FNumber-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FCreate-or-delete-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FCreate-or-delete-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20i%20guess%20in%20many%20other%20places%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-44356%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%20separator%20(comma)%20inside%20a%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-44356%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20Mr.%20Baklan%2C%20this%20is%20exactly%20what%20I%20want%20%3Asmileyvery-happy%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EBut%20I%20have%20noticed%20another%20code%20gives%20the%20same%20result.%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20code%20is%3A%20%23%2C%23%230%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EWhat's%20the%20difference%20between%20it%20and%20the%20code%20you%20mentioned%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-44147%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%20separator%20(comma)%20inside%20a%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-44147%22%20slang%3D%22en-US%22%3E%3CP%3EHaytham%2C%20format%20the%20SUM%20as%20well%2C%20like%26nbsp%3B%3D%22Total%3A%20%22%20%26amp%3B%20TEXT(SUM(D3%3AD12)%2C%22%23%2C%23%230_%20%3B-%23%2C%23%230%20%22).%20If%20I%20understood%20your%20question%20correctly...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-567936%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%20separator%20(comma)%20inside%20a%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-567936%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20write%20a%20formula%20where%20I%20am%20using%20a%20cell%20and%20a%20rule.%20Below%20explains%20what%20i%20am%20trying%20to%20achieve%3C%2FP%3E%3CP%3EA1%20-%20%241%2C000%3C%2FP%3E%3CP%3E%3DA1%20%26amp%3BIF(A1%26lt%3B0%2C%22CR%22%2C%22%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20result%20i%20get%20from%20this%20is%201000CR.%20I%20cannot%20get%20it%20to%20format%20to%20%241%2C000CR%20if%20cell%20A1%20is%20less%20than%200.%20PLEASE%20HELP%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-568108%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20add%20separator%20(comma)%20inside%20a%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-568108%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F342187%22%20target%3D%22_blank%22%3E%40Heelllo%3C%2FA%3E%26nbsp%3B%2C%20you%20may%20apply%20custom%20number%20format%20to%20your%20cell%20like%3C%2FP%3E%0A%3CPRE%3E%5B%24%24-en-US%5D%23%2C%23%230%3B-%5B%24%24-en-US%5D%23%2C%23%230%22CR%22%3B%5B%24%24-en-US%5D%23%2C%23%230%3C%2FPRE%3E%0A%3CP%3Eor%2C%20if%20with%20formula%2C%20use%3C%2FP%3E%0A%3CPRE%3ETEXT(A1%2C%22%5B%24%24-en-US%5D%23%2C%23%230%22)%20%26amp%3B%20%22CR%22%3C%2FPRE%3E%0A%3CP%3Ewith%20IF%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Haytham Amairah
Trusted Contributor

How to separate the value of SUM by comma in this case?

Capture.JPG

 

 

 

 

 

7 Replies
Highlighted
Solution

Haytham, format the SUM as well, like ="Total: " & TEXT(SUM(D3:D12),"#,##0_ ;-#,##0 "). If I understood your question correctly...

Thank you Mr. Baklan, this is exactly what I want :smileyvery-happy:

 

But I have noticed another code gives the same result.

The code is: #,##0

What's the difference between it and the code you mentioned?

Actually no difference, for me it was faster enter the formula ans copy/paste it here. In general, for whole numbers you may use something like TEXT(A1,"#,#"). Another story if you would like to make different formatting for positive and negative numbers, add text and or colors into formatting, etc - all that additional symbols matter.

 

In particular, you may format your cell with =SUM(D3:D12) as "Total: "#,### (enter this into the Custom format of the cell) that gives the same result.

 

Comma just separates your number on thousans, millions, billions, etc.

 

More about formatting is here

https://support.office.com/en-us/article/Number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68

https://support.office.com/en-us/article/Create-or-delete-a-custom-number-format-78f2a361-936b-4c03-...

 

and i guess in many other places

 

 

Sergei Baklan

If i want to use decimal places in the number, Formula?

I am trying to write a formula where I am using a cell and a rule. Below explains what i am trying to achieve

A1 - $1,000

=A1 &IF(A1<0,"CR",""

 

The result i get from this is 1000CR. I cannot get it to format to $1,000CR if cell A1 is less than 0. PLEASE HELP

 

@Heelllo , you may apply custom number format to your cell like

[$$-en-US]#,##0;-[$$-en-US]#,##0"CR";[$$-en-US]#,##0

or, if with formula, use

TEXT(A1,"[$$-en-US]#,##0") & "CR"

with IF

 

 

Related Conversations