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%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%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%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
Haytham Amairah
Trusted Contributor

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

Capture.JPG

 

 

 

 

 

10 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

 

 

@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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies