How to add separator (comma) inside a formula?

[Haytham Amairah]

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

[#2 · Accepted solution]

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

[#3]

Thank you Mr. Baklan, this is exactly what I want

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?

[#4]

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

[#5]

Sergei Baklan

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

[#6]

Please check this https://www.ablebits.com/office-addins-blog/2016/07/07/custom-excel-number-format/

[#7]

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

[#8]

@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

[#9]

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

[#10]

@SergeiBaklan 

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 

[#11]

@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

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

[#12]

=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

[#13]

Hi, I was hoping you could help me with getting a comma into my formulas/cells as well?
They're two formulas that are roughly the same, and they're more about formatting data from other cells in a specific way. While the numbers in the other cells that are being sourced/cited have the separator comma, the result in the formula does not. I've tried various ways of using TEXT to add it, and other standard formatting options, but I think the way the numbers are formatted in the final cell with these 'formulas' is confusing the Excel commands.

The formulas (as given to me) are:
=E4&" / "&"$"&F4&" "&G4
=L4&" / "&"$"&M4&""
The results look like, e.g.:
4 / $2386 -248
3 / $8586

So is there either a way to add a separator comma to those $ numbers, OR is there a better way to achieve these results in a single cell?
Thanks!

[#14]

@beck102015 

That could be

=E4 &" / " &  TEXT(F4, "$#,##0") & " "& TEXT(G4, "#,##0")

[#15]

That's it! Thank you so much!

[#16]

@beck102015 , you are welcome

[#17]

@SergeiBaklan hi can you help me i want to use VLOOKUP formula but when i add "," camma it shows no  reaction and i can't add other pyrometer so how can i fix it? 

[#18]

@melika1213 

Who knows, perhaps you shall use semicolon instead of comma in your locale, or something else. Better if you start new discussion here https://techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral with your question, giving more details which exactly formula you try to use and on which data.