CODEWITHSUNDEEP
excel
Noby Fujioka
Noby Fujioka

Reputation: 1834

How to keep thousand separators (,) of decimal numbers while hiding unnecessary zeros in excel

I hope someone can help me with this. I have searched for an answer on the internet, but I have not come across with an exact answer I am looking for to solve my formating issue in Microsoft Excel.

In Excel, I would like to display numbers as below for example.

1,222,252.042
1,643,123
2,334,234.1
1,235.02
0.204

I do not want to have zeros after the smallest decimal unit. For example, 0.1 should be 0.1, not 0.100. I do not want trailing decimal point. For example, 54 should be 54, not 54.(dot). Also, the thousand separator should be kept. For example, 4,567 should be 4,567, not 4567.

I do not want below (more examples).

1,222,252.0420 
1,643,1230
2,334,234.1000
1,235.0200
0.2040

I do not want below, either.

1222252.042
16431230.
2334234.1
1235.02
0.204

I formatted cells with custom cell format #,###,##0.#### But, I cannot hide decimal dots when the numbers are whole numbers. For example, 1,234 becomes 1,234.(dot). I cannot use general format, because it will not show the thousand separators.

I would appreciate it very much if you could help me on this.

Kind regards

Upvotes: 1

Views: 3190

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34265

You could use conditional formatting to apply a different format for numbers without a decimal part:-

Highlight the range of your numbers

Go to Conditional formatting | New Rule | Use a formula

Put in (if your list of numbers starts in C2 as below)

    =C2=INT(C2)

with the format

    ###,###,##0

enter image description here

Upvotes: 1

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3833

I don't have the reputation to comment, so I'll just add to the response from @Tom Sharpe by saying that as discussed on this page http://www.ozgrid.com/Excel/excel-custom-number-formats.htm there is no way to mark whether the decimal shows up or not based on the size of the number, within the custom format options. You can choose alternative formatting for positive numbers, negative numbers, zeroes, and text. Any other "variable" formatting must be based on the available character set shown in the table on ozgrid's discussion.

ie: "#,##0.###" will always show a zero, may show additional digits to the left of the zero if they exist, will always show a decimal, and may show additional digits to the right of the decimal if they exist.

Note that while you may think that this optional formatting method is possible based on how Excel reacts when you type in a number without setting formatting, that is actually Excel taking your input and deciding which format is best for your data, which is not the same thing as having a consistent format which fluctuates based on whether you have any decimals or not.

So, if you want any other form of formatting to change, you will need to use a conditional formatting rule as noted in the answer above.

Upvotes: 0

Related Questions