How to remove periods in numbers in Excel

When typing a string of numbers, data entry people have a habit of inserting dots in the middle to easily observe the data and reduce errors when entering data. However, in some cases, the dot will make the calculation impossible. So in this article, Dexterity Software will introduce you to read the article: How to remove periods in numbers in Excel.

How to remove periods in numbers in Excel Picture 1

Remove the period with the Find & Replace tool

Let's say you have the original series of numbers like the worksheet below:

How to remove periods in numbers in Excel Picture 2

Step 1: You copy the original data into the area to paste. If you are editing on the original data without saving it, skip this step.

How to remove periods in numbers in Excel Picture 3

Step 2: Highlight the data area to delete the dot. Note that you must do this step to avoid affecting the content of other cells.

How to remove periods in numbers in Excel Picture 4

Step 3: Press Ctrl + H to open the Find & Replace window . The Find & Replace window appears . In the Find what: section enter; Replace with: leave blank then press the Replace All button to delete all the dots in the selection in step 2. Or press the Replace button to delete one dot at a time.

How to remove periods in numbers in Excel Picture 5

The dot results in the cells in the data range that you selected step 2 have been deleted:

How to remove periods in numbers in Excel Picture 6

Remove the period with the SUBSTITUTE function

The structure of the SUBSTITUTE function is as follows: SUBSTITUTE (text, old_text, new_text, [instance_num]). Inside:

1. Text: Required argument. The original text you want to replace the characters with. or may be a location that refers to the cell containing the original character.
2. Old_text: Required argument. The character you want to replace.
3. New_text: Required argument. New character to replace old_text.
4. Instance_num: An optional argument, the location of the old_text replacement. If omitted, every time the old text appears, it will be changed to new text.

For the above example, you use the following SUBSTITUTE formula: = SUBSTITUTE (cell location, "0", "") .

Results:

How to remove periods in numbers in Excel Picture 7

Copy the data for the remaining cells in the column you get the following result:

How to remove periods in numbers in Excel Picture 8

Note that the results obtained are non-numeric formula data, if you accidentally delete the data in the original cell, the results also change.

How to remove periods in numbers in Excel Picture 9

Above Dexterity Software has taught you two ways to remove periods in numbers in Excel. Good luck!