SOLVED

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%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%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%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%3CIMG%20id%3D%22smileyvery-happy%22%20class%3D%22emoticon%20emoticon-smileyvery-happy%22%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Fi%2Fsmilies%2F16x16_smiley-very-happy.png%22%20alt%3D%22Smiley%20Very%20Happy%22%20title%3D%22Smiley%20Very%20Happy%22%20%2F%3E%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%3CLINGO-SUB%20id%3D%22lingo-sub-977228%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-977228%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3Bplease%20help%20me%20fix%20this%20formula%26nbsp%3B%3C%2FP%3E%3CP%3E%3D(%225%25%20VAT%20ON%20AED%22)%26amp%3BROUND(SUM(H36%3AH37)%2C2)%20(%20the%20output%20in%20the%20sum%20i%20need%20to%20add%20Coma%20like%20in%20accounting%20format)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-977237%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-977237%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20help%20me%20fix%20this%20formula%26nbsp%3B%3C%2FP%3E%3CP%3E%3D(%225%25%20VAT%20ON%20AED%22)%26amp%3BROUND(SUM(H36%3AH37)%2C2)%20(%20the%20output%20in%20the%20sum%20i%20need%20to%20add%20Coma%20like%20in%20accounting%20format)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%20me%20with%20this%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-978177%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-978177%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F439827%22%20target%3D%22_blank%22%3E%40suresh_k8309%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20you%20may%20apply%20desired%20accounting%20format%20to%20the%20cell%2C%20Ctrl%2B1%2C%20select%20Custom%20and%20add%20your%20text%20in%20front%20of%20each%20formatting%20block%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20544px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F154622i0055FF69BDD3B3AB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAlternatively%20wrap%20ROUND%20with%20TEXT%20function%20applying%20accounting%20format%20string%20for%20your%20locale.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578242%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-1578242%22%20slang%3D%22en-US%22%3E%3CP%3E%3DLARGE(N6%3AN15%2C1)%26amp%3B%22%20on%20first%20%22%26amp%3BLARGE(N6%3AN15%2C2)%26amp%3B%22%20on%20second%20%22%26amp%3BLARGE(N6%3AN15%2C3)%26amp%3B%22%20on%20third%20%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20can%20we%20add%20separator%20commas%20in%20this%20formulae.%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETHanks%3C%2FP%3E%3C%2FLINGO-BODY%3E
Trusted Contributor

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

Capture.JPG

 

 

 

 

 

11 Replies
Best Response confirmed by Haytham Amairah (Trusted Contributor)
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

 

 

@Haytham Amairah please help me fix this formula 

=("5% VAT ON AED")&ROUND(SUM(H36:H37),2) ( the output in the sum i need to add Coma like in accounting format) 

 

@Sergei Baklan 

please help me fix this formula 

=("5% VAT ON AED")&ROUND(SUM(H36:H37),2) ( the output in the sum i need to add Coma like in accounting format) 

 

Please help me with this 

@suresh_k8309 

As variant you may apply desired accounting format to the cell, Ctrl+1, select Custom and add your text in front of each formatting block

image.png

Alternatively wrap ROUND with TEXT function applying accounting format string for your locale.

=LARGE(N6:N15,1)&" on first "&LARGE(N6:N15,2)&" on second "&LARGE(N6:N15,3)&" on third "

 

Hi can we add separator commas in this formulae.?

 

THanks