Format a number with optional decimal places in Excel
Problem
I have numbers in cells in Excel. I want the numbers formatted so that if they have decimal places they show to a maximum of two, and if they have no decimal places it doesn't show any. For example. 15 should be formatted as 15, NOT 15.00 14.3453453 should be formatted as 14.35 12.1 should be formatted as 12.1 0 should be formatted as 0 The closest custom format code I've come up with is . Unfortunately this formats as 15. (note the extra decimal point). To further complicate the issue, the spreadsheet is a result of an export from SQL Server Reporting Services. So no macros are possible. Oh well, it looks like is my best bet, and they can just live with the extra period.
Error Output
0.##
Unverified for your environment
Select your OS to check compatibility.
1 Fix
Fix for: Format a number with optional decimal places in Excel
Alternatively, you can solve the "optional" decimal point problem using the following solution: Number Format: This will add the decimal place and fractional value accordingly, while formatting negative numbers in a more legible way. As for the poster's original question, you still need to round/truncate the "extra" decimal points using a formula. However, this is a simple formula of that is not extremely computationally expensive. Examples:
Awaiting Verification
Be the first to verify this fix
Sign in to verify this fix