Buzz
Removing Decimal Points in Excel Number Sequences
In this tutorial, Mytour will use Excel 2016 to demonstrate removing decimal points in Excel number sequences.
Suppose you have number sequences with decimal points as shown below:
To remove the decimal points in these number sequences, you can choose one of three methods below:
1. Using Find and Replace Tool to Remove Decimal Points
Step 1: Open the Find & Replace window in Excel by pressing the shortcut Ctrl + H.
Step 2: In the Find & Replace window, follow these steps to remove decimal points.
- Find what: Enter the decimal point.
- Replace with: Leave this field empty.
Step 3: Click Replace to remove decimal points in each cell; or click Replace All to remove all decimal points in all other cells (see the image above).
As a result, you have successfully removed decimal points in the number sequences in Excel.
* Note: To accurately remove decimal points in the sequences you want, you should highlight and select before clicking Replace All to ensure precise deletion. This avoids unintended removal of decimal points '.' that you do not want to delete because they were not selected, causing the application to default to deleting all.
2. Using the Substitute Function to Remove Decimal Points
With this method, you will use the Substitute function to remove decimal points in Excel number sequences.
Step 1: In cell D3, enter the formula as shown in the image below:
The function =SUBSTITUTE(C3,'.','') helps you replace the decimal point '.' in cell C3 with an empty character, which is equivalent to removing the decimal point '.' from cell C3.
To understand more about the syntax and detailed usage of this function, you can refer to the detailed article on the SUBSTITUTE function in Excel that Mytour has shared to learn how to use it.
Step 2: After entering the formula -> press Enter to display the calculation result. The result is that you will successfully remove the decimal point as shown in the image below:
Step 3: Click the first result cell and then drag down to the remaining cells to complete the process of removing the decimal points in the cells below.
3. Using Format Cells to Eliminate Decimal Points
In this scenario, when your numbers are set with Decimal places and have the Use 1000 Separator (.) option checked
For example, as shown below, you have set Decimal places to display the decimal point (.) in Excel.
To remove the decimal points, follow these simple steps:
Step 1: Select (highlight) the number sequences you want to remove the decimal points from -> right-click and choose Format Cells or press the shortcut key Ctrl + 1
Step 2: On the Number tab -> select Number and:
- Decimal places: Enter the number 0
- Use 1000 Separator (.): Uncheck the tick box in front -> then click OK.
Step 3: The result is that the decimal points in the number sequence have been successfully removed.
So, with just the 3 simple methods performed above, you have successfully removed decimal points from number sequences in Excel. With these 3 methods, you can easily do the same on other versions of Excel such as Excel 2013, 2010, 2007, 2003 quickly and easily.
In case you want to convert decimal points to commas to standardize the display of decimal numbers, fractional units according to the current Excel sheet, you can also easily convert them quickly. For a clearer understanding of the conversion process, refer to the article converting decimal points to commas in Excel shared by Mytour to learn how to do it. Wish you success!
