How to remove all zeros after decimal in Excel?
If you have a list of decimal numbers which contain zeros after the decimal point, now, you need to remove all the trailing zeros from the numbers to get the following result. How could you solve this task in Excel?
In Excel, you can apply the Format Cell function to remove the trailing zeros after decimal by a custom format, please do as follows:
1. Select the number list that you want to remove the trailing zeros after decimal point, then right click and choose Format Cells from the context menu, see screenshot:
2. In the Format Cells dialog box, click Number tab, then click Custom option from the Category pane, and then enter 0.#### into the Type text box, see screenshot:
3. And then click OK button, all the trailing zeros have been removed after the decimal point as following screenshot shown:
Note: There will be left with the trailing period if your number is whole number with zeros after the decimal.
You can’t handle the trailing period by number formats with Format Cells function, but the following formula can help you to deal with it.
Enter this formula: =SUBSTITUTE(TRIM(SUBSTITUTE(A2,"."," "))," ",".") into a blank cell where you want to put the result, then drag the fill handle down to the cells that you need to apply this formula, and all the trailing zeros are removed after the decimal, see screenshot:
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 3 months agoFormat cells, select General.
- To post as a guest, your comment is unpublished.· 2 years agomuch simpler way.. select column of numbers, select Format cells, Leave it on Numbers.. ..change Decimal points to zero.. bingo.. no trailing zeros or annoying decimal dot.
- To post as a guest, your comment is unpublished.· 1 years agoBut if some values have important decimals and others just have ".0" you lose ALL decimals
- To post as a guest, your comment is unpublished.· 2 years agoThis has not helped me.