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.
Remove the period with the Find & Replace tool
Let's say you have the original series of numbers like the worksheet below:
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.
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.
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.
The dot results in the cells in the data range that you selected step 2 have been deleted:
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:
Copy the data for the remaining cells in the column you get the following result:
Note that the results obtained are non-numeric formula data, if you accidentally delete the data in the original cell, the results also change.
Above Dexterity Software has taught you two ways to remove periods in numbers in Excel. Good luck!
You should read it
- 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
May be interested
- How to paste data into non-hidden cells, ignoring hidden cellsthe copy tool - paste is a useful tool in excel. however, to best use this tool, not all users know it. in this article, dexterity software teaches you how to paste data that ignores hidden cells.
- 3 How to rotate a PDF file and save it as the simplest new fileyou have a pdf document and you need to read it, but when you open it, all the words are rotated horizontally or reversed? use one of the three ways to rotate a pdf file and save it as the simplest new file that tipsmake.com introduces below.
- How to use Vlookup function between 2 sheets, 2 different Excel fileshow to use vlookup function between 2 sheets, 2 different excel files. normally the vlookup function applies on the same file in a certain workbook, but in some cases where the work is not so simple, the condition area and the area you need to access the data belong to belongs to two other sheets. together
- How to correct a #NAME error in Excelerror #name? an error when excel cannot recognize what it is, it can be a function or an argument. common causes of #name errors? either because the function name is misspelled, or one of the following is caused:
- How to fix the error can not save Excel filehow to fix the error can not save excel file. in some cases, you can open a spreadsheet directly on outlook, or open a file as read only; you can edit the file, but when saving the file, excel will alert you with the following content:
- How to fix non-unhide error in Excelwhen working on an excel spreadsheet, there are times when you clearly see that there are hidden lines (hide) that you cannot open offline (unhide). why is that happening? let's have a look at dexterity software through the article below.