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:
- 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.
- Old_text: Required argument. The character you want to replace.
- New_text: Required argument. New character to replace old_text.
- 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!
You should read it
- Instructions to stamp negative numbers in Excel
- How to Replace Comma with Period in Excel
- PI (PI Function) in Excel - How to use PI numbers in Excel
- How to remove spaces in Excel
- Split numbers from strings in Excel
- How to delete dots in a series of numbers on Excel
- How to extract numbers or text from Excel
- How to format numbers in Excel
- How to convert negative numbers to positive numbers Excel
- How to Remove Spaces Between Characters and Numbers in Excel
- How to convert Apple Numbers documents to Microsoft Excel
- How to convert numbers into words in Excel?
May be interested
How to paste data into non-hidden cells, ignoring hidden cells
3 How to rotate a PDF file and save it as the simplest new file
How to use Vlookup function between 2 sheets, 2 different Excel files
How to correct a #NAME error in Excel
How to fix the error can not save Excel file
How to fix non-unhide error in Excel