Buzz
When typing a sequence of numbers, people often habitually insert decimal points to ease data observation and reduce input errors. However, in certain cases, these decimal points may hinder proper calculations. Therefore, in this article, Software Tricks introduces readers to the article: Removing decimal points in Excel number series.
Removing decimal points using the Find & Replace tool
Suppose you have an original number series like the spreadsheet below:
Step 1: Copy the original data into the target area. If you are directly editing the original data without saving, skip this step.
Step 2: Highlight the data area where decimal points need to be removed. Note that this step is mandatory to avoid affecting the content of other cells.
Step 3: Press the Ctrl + H key combination to open the Find & Replace window. The Find & Replace window appears. In the Find what: field, enter the decimal point; leave the Replace with: field blank, then click the Replace All button to remove all decimal points in the selected area from step 2. Or click the Replace button to remove each decimal point one by one.
The decimal points in the cells within the data range you selected in step 2 have been removed:
Get rid of dots with the SUBSTITUTE function
The structure of the SUBSTITUTE function is as follows: SUBSTITUTE (text, old_text, new_text, [instance_num]). Where:
- Text: Mandatory parameter. The original text you want to replace characters. or it can be a reference to the cell containing the original characters.
- Old_text: Mandatory parameter. The character you want to replace.
- New_text: Mandatory parameter. The new character to replace old_text.
- Instance_num: Optional parameter, is the position to perform replacing old_text. If omitted, each occurrence of old text will be changed to the new text.
In the example above, you use the SUBSTITUTE formula as follows: = SUBSTITUTE (cell reference, '0', '').
Result obtained:
Copy data for the remaining cells in the column, you get the following result:
Note that the results obtained are data in formula format, not numerical format. If you accidentally delete the data in the original cell, the result will also change.
Above, Software Tricks has guided you through two ways to remove dots in numbers on Excel. Wish you success!
