CODEWITHSUNDEEP
excelformat
Dolphin975
Dolphin975

Reputation: 331

Excel - dynamic formatting for decimal places

I have a column in which the user enters integers and also numbers with decimals. I'd like to format the cells so that when the user enter an integer or a number with 2 decimals, the format is like "90.00", "22.56", "1.00", etc but when the user enters "12.34343" or "99.23131232" the cell displays all the decimals. Any help would be really appreciated.

Upvotes: 1

Views: 1797

Answers (1)

Axel Richter
Axel Richter

Reputation: 61860

See Number format codes -> Decimal places, spaces, colors, and conditions.

The number format code 0 means display this digit in all cases whether it is significant or not. The number format code # means display this digit only if it is significant.

So the number format code for your requirement would be:

0.00#############

That means: At least one digit before the decimal point. Further significant digits before decimal point also will be displayed. At least two digits after decimal point independent whether significant or not. Then other digits (3, 4, 5, ..., 15) after decimal point but only if those are significant.

Upvotes: 2

Related Questions