• 396K Members
• 5,327 Online
• 431K Conversations
SOLVED

## How to add separator (comma) inside a formula?

Trusted Contributor

# How to add separator (comma) inside a formula?

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

7 Replies
Solution

# Re: How to add separator (comma) inside a formula?

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

# Re: How to add separator (comma) inside a formula?

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?

# Re: How to add separator (comma) inside a formula?

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

# Re: How to add separator (comma) inside a formula?

Sergei Baklan

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

# Re: How to add separator (comma) inside a 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

# Re: How to add separator (comma) inside a formula?

@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
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies