9 Ways to Remove Leading Zeroes in Excel
Excel is programmed to remove the leading zeroes from the worksheets by nature. But sometimes you may find the leading zeroes in the dataset appearing when you insert the numbers as text or customize the cell formatting. No matter what the reason is, in this article, I will explain to you how you can remove the leading zeroes from Excel. So let’s get into the main part!
Introduction to the Dataset
In this dataset, the Raw Data ranges from cells A2:A10. All the cells in column Raw Data have 0 at the beginning, yet the number of zeroes is different in all cells.
In column Output, I will deliver the final results.
Case #1: Remove Leading Zeroes in Excel If the Cells Contain Text Values
Method #1.1: Use Error Checking Icon to Remove Leading Zeroes in Excel
When your cell format is set to Text, you may find the leading zeroes visible in the cell addresses. The best way to remove them is to use the Error Checking command.
Usage Guide
Step_1: Select the cell range A2:A10.
Step_2: Click on the yellow alert icon beside cell A2.
Step_3: Select the option Convert to Number.
Final Result >
Following these three super easy steps, you can remove the leading zeroes in Excel sheets.
Method #1.2: Use the VALUE Function to Remove Leading Zeroes in Excel
The VALUE function is used to convert text strings into numerical values.
Usage Guide
Step_1: Type this formula in cell B2:
=VALUE(A2)
Formula Explanation
- A2 is the input of the cell address that has the text.
Step_2: Press ENTER to apply the formula.
Step_3: Take the cursor to the right-bottom corner of cell B2 and drag the Fill Handle icon in cell range B2:B10.
The Fill Handle will copy the formula from cell B2 to cell B10.
Final Result >
Using the VALUE function is also an easy method to remove the leading zeroes.
Method #1.3: Use Flash Fill to Remove Leading Zeroes in Excel
The Flash Fill is one of the magic tools of Microsoft Excel. I prefer using Flash Fill when I want to avoid using the functions and my output has specific patterns.
Usage Guide
Step_1: Without the leading zeroes, enter the values from cells A2 and A3 in cells B2 and B3, respectively.
You have to manually type the values in cells B2 and B3. Alternatively, if you have long text strings in cells A2 and A3, you can copy the values, paste them, and manually remove the leading zeroes from B2 and B3.
Step_2: Now, go to the Data tab.
Step_3: Click on Flash Fill from the Data Tools group.
Final Result >
The Flash Fill command normally does the job perfectly. But I will recommend that you skim through the answers after applying the Flash Fill.
Method #1.4: Multiplying the Numbers with 1 to Remove Leading Zeroes in Excel
Another technique to remove the leading zeroes is to multiply the Raw Data by 1 in an empty column.
Usage Guide
Step_1: In cell B2, insert this formula:
=A2*1
Step_2: Press ENTER.
Step_3: Drag the Fill Handle from cell range B2:B10.
Final Result >
The formula vanished the leading zeroes from the text strings.
Method #1.5: Use the Paste Special Command to Remove Leading Zeroes in Excel
Another way is to multiply an empty cell with the Raw Data to remove the zeroes. If you apply this method, you can get the results in its own cell address, A2:A10.
Usage Guide
Step_1: Select an empty cell, B2.
Step_2: Now press the CTRL+C key.
It will copy cell B2.
Step_3: Select the cell range A2:A10.
Step_4: Right-click on the cell selection.
The context menu will appear beside the cell range.
Step_5: Select Paste Special.
The Paste Special dialog box will pop up on the worksheet.
Step_6: Select the Add radio button from the Operation Section.
Step_7: Hit the OK button.
Final Result >
The values removed the leading zeroes from their text strings in cell A2:A10.
Method #1.6: Use Text to Columns Option to Remove Leading Zeroes in Excel
To remove starting zeroes from text strings with the Text to Columns command, follow the Usage Guide given below.
Usage Guide
Step_1: Select the cell range A2:A10.
Step_2: Go to the Data tab.
Step_3: Select the Text to Columns command from the Data Tools group.
The Convert Text to Columns Wizard will show up on your Excel sheet.
Step_4: Select the Delimited radio button.
Step_5: Click on Next.
Step_6: Unmark all the Delimiters.
Step_7: Click on Next.
Step_8: Select the General button from Column Data Format.
Step_9: In the Destination box, type B2 or $B$2.
Step_10: Hit the Finish button.
Final Result >
The results will appear in cell B2, just like in the image below:
Method #1.7: Use VBA to Remove Leading Zeroes in Excel
You can use VBA (Visual Basic for Applications) to remove leading zeroes in Excel cells using a custom VBA code.
Usage Guide
Step_1: Press ALT + F11 to open the Visual Basic Editor.
Step_2: Then select Insert ⋙ Module to open a new module.
Step_3: Paste the VBA code into the new module.
Sub RemoveLeadingZeros()
Dim rng As Range
Dim cell As Range
Dim strValue As String
' Set the range where you want to remove leading zeros
Set rng = Range("A1:A100") ' Change the range to your desired range
For Each cell In rng
If cell.Value <> "" Then
' Remove leading zeros from the cell value
strValue = CStr(cell.Value)
strValue = Application.Trim(strValue)
cell.Value = strValue
End If
Next cell
End Sub
For this dataset, I am using Range(“A2:A10”) as the range in the code where I want to remove leading zeroes. You can change it to your desired range, such as “A1:A50” or “B2:B200”, etc.
Step_4: Close the VBA editor by clicking the close button.
Shortcut: Press the ALT + Q keys to close the VBA editor.
Step_5: Go to the Developer tab.
Step_6: Select Macro from the Code group.
This will run the Macro.
Shortcut: Press ALT + F8 to run the Macro.
The Macro window will pop up on the Excel sheet.
Step_7: Select the RemoveLeadingZeros macro from the list.
Step_8: Click on the Run button.
Final Result >
If you are comfortable with using VBA in Excel, you may find this method the easiest of all. The result is in the following image:
Case #2: If the Cells in Excel Sheet Have Numeric Values
A rare case is when the Number Format is changed to a Special Number Format. Even though leading-up zeros are usually visible because they are in text format, this does happen occasionally.
Usage Guide
Step_1: Select cell range A2:A10.
Step_2: Go to the Home tab.
Step_3: Click on the Format Cells drop-down menu from the Number group.
Step_4: Select More Number Formats.
The Format Cells dialog box will appear on the screen.
Step_5: Go to Custom.
Step_6: Remove the zeros from the Custom Type and keep an empty box.
Step_7: Click on the OK button.
Final Result >
You must make sure that your cell format is Special Number Format before using this procedure. Otherwise, this technique won’t be effective.
Case #3: If the Cells in Excel Contain Both Numeric and Non-numeric Values
This method is especially useful for removing leading zeroes from text strings that contain both numeric and non-numeric values in the cell addresses. However, you can also use this approach to remove numeric values.
Usage Guide
Step_1: Enter the following formula in cell B2:
=RIGHT(A2,LEN(A2)-FIND(LEFT(SUBSTITUTE(A2,"0",""),1),A2)+1)
Formula Explanation
- SUBSTITUTE(A2, “0”, “”) replaced all occurrences of the character “0” in the text in cell A2 with an empty string “”.
- LEFT(SUBSTITUTE(A2, “0”, “”), 1) extracted the first character from the result obtained in SUBSTITUTE(A2, “0”, “”).
- FIND(LEFT(SUBSTITUTE(A2, “0”, “”), 1), A2) located the position of the first non-zero character (obtained in LEFT(SUBSTITUTE(A2, “0”, “”), 1)). It returns the position of the first occurrence of the non-zero character.
- LEN(A2) – FIND(LEFT(SUBSTITUTE(A2, “0”, “”), 1), A2) + 1 calculated the number of characters from the first non-zero character to the end of cell A2. It does so by subtracting the position of the first non-zero character from the total length of A2 and adding 1. The +1 is added to include the first non-zero character itself in the count.
- RIGHT(A2, LEN(A2) – FIND(LEFT(SUBSTITUTE(A2, “0”, “”), 1), A2) + 1) extracted a substring from cell A2.
Step_2: Drag down the Fill Handle of cell B2 at the bottom-right corner to apply the formula from cell B2 to B10.
Final Result >
Now, you have the values with the leading zeros removed in Output (column B).
Conclusion
In this article, I have discussed nine methods to remove the leading zeroes from text strings in Excel. I hope you’ve found this article quite helpful. If you have any questions regarding this topic, please let me know in the comment section below.
