How to Remove Leading Zeros in Excel? [7 Easy Methods]

Leading zero is essential for creating fixed-length numbers, while they are not significant in most cases.

Moreover, it causes errors or puzzlement while performing calculations in Microsoft Excel.

It might be very frustrating to deal with a list of numbers with a leading zero.

This article will demonstrate how to remove the leading zero in Excel using various methods.

Regardless of your needs, you can indeed find a suitable solution here.

Reasons You May Have Leading Zero in Excel

Leading Zero is any zero that comes before any non-zero digits in a number.

Removing the leading zero is common while working with numerical data in MS Excel.

Here are some predictable reasons you may have leading zero in Excel.

How to Remove Leading Zeros in Excel? [7 Easy Methods]

By default, MS Excel automatically removes leading zero, but you may need different ways to remove it manually in some cases.

Removing a leading zero in Excel is not annoying but removing a large amount of a leading zero is problematic.

First, you must find the causes; then, it will be easier to predict the proper methods to remove the leading zero.

Here we have discussed different methods to remove leading zero in MS Excel.

Without further ado, let’s get started!

1. Convert the Text to Numbers

Sometimes the number in your Excel sheet may not act like numbers and may even produce errors.

In this case, they look like numbers but are text strings.

We use the text-to-number option to remove the leading zero in Excel.

1. First, open the Excel sheet and select the relevant cells where you want to remove the leading zeros.
2. Click the Exclamation point error icon(!) and choose Convert to Number options from the drop-down menu.



3. It will convert the selected cells to numbers by removing the leading zero.

2. Change the Custom Number Formatting

If you want all numbers to show up to six digits, and if you have a four-digit number, then Excel will automatically add two leading zeros.

When your cell is formatted to show a specific number of digits in each number, then you will see a leading zero in your cell.

Custom number formatting only changes the look of the cell but not the cell type.

You can remove the leading zero with just a few clicks. Follow the steps below to get rid of the leading zero.

1. First, open the Excel sheet and select the relevant cells where you want to remove the leading zeros.
2. Click the Home tab on the left-hand side upper menu bar and Expand icon.



3. Select the Number under Format Cells from the drop-down menu.
4. Again, select General options from the drop-down menu and click Ok.



5. The format of the customs number will be changed, and the number will appear exactly as entered.

3. Use the Paste Special Technique

Paste special method works in both conditions, whether the number is in text string format or application of custom number formatting.

Follow the steps below to remove leading zeros in Excel using the paste special technique;

1. First, select the relevant cells where you want to remove the leading zeros.
2. Copy the selected cell or press Ctrl+ C to copy the selected cells.
3. Right-click on the selected cell and choose the Paste Special option from the drop-down option.



4. Then, click Add option under the operation and click the Ok button.

The above method removes all the leading zeros and apostrophes from the selected cells.

This method applies to numbers only. If there is any text string in the cell, it will be unchanged.

4. Multiply the Column by 1 or Add 0

It is one of the easiest ways to remove the leading zero without changing its original value from the Excel Sheets.

Remember that the calculation is possible with the numbers only but not with the text strings.

Follow the steps below to multiply the column by 1 or add 0;

1. To begin, arrange the dataset and select the cell from which you want to remove the leading zeros.
2. Insert a formula in the C2 cell, i.e., =B2*1. Similarly, do it for the respective columns such as B3, B4, B5 and so on.
3. Finally, hit the Enter button, and you will get the desired result in that cell.



4. Alternatively, you can also add 0 to remove leading zeros.
5. Like above, insert the formula in the C2 cell, i.e., =B2+0. You can repeat the same for B3, B4, B5 and so on.



6. The method removes all the leading zeros from the selected cells. Here is the final result.

5. Use Value Function

Another way to remove the leading zero is by using the Value function. Using the Value function, you can convert text strings into numbers.

This function works in both conditions, whether the number is in text string format or application of custom number formatting.

The formula for removing the leading zero is =VALUE(X1), where X is the destination cell.

Follow the steps below to remove leading zeros in Excel using the value function;

1. First, arrange the dataset and select the relevant cell where you want to remove the leading zeros.
2. Insert a formula in the C2 cell, i.e., = VALUE(B2). Similarly, do it for the respective columns such as  B3, B4, B5 and so on.
3. Finally, hit the Enter button from the Keyboard, and you will get the desired result in that cell.

6. Use Text to Columns

Text-to-column features enable you to split cell data into multiple columns while removing the leading zero.

You can follow the steps below to remove the leading zero using text to columns features.

1. First, select the range of cells where you want to remove the leading zero.
2. Click on the Data tab from the upper manual panel.
3. Click Text to Columns under Data options.



4. Enable the Delimited under file type and click the Next button.



5. Uncheck all the options (tab, semicolon, comma, space, and other) under the Delimiters and click the Next button.



6. Select the destination cell and click Finish (In this case, B4 is the destination cell).

The method mentioned above eliminates all leading zeros and gives the exact number.

7. Using VBA (Virtual Basic Analysis)

VBA is an Excel tool that uses a formula to deal with non-numerical values.

Follow the steps to remove the leading zero through the VBA method;

1. Firstly, select the range of cells where you want to remove the leading zero.
2. Go to the Developer option and then click Visual Basic.



3. Under the Visual Basic option, click the Insert tab and select Module.



4. Here, copy and paste the Macro code below.
Sub Remove_Leading_Zeros()
Dim Delete_Range As Range
Dim Work_Range As Range
On Error Resume Next
xTitleId = “Remove Leading Zeros”
Set Work_Range = Application.Selection
Set Work_Range = Application.InputBox(“Range”, xTitleId, Work_Range.Address, Type:=8)
Work_Range.NumberFormat = “General”
Work_Range.Value = Work_Range.Value
End Sub
5. The code will be used to convert numeric values to General format. General formatting of numbers removes the leading zero in Excel.



6. Press the F5/Fn+F5 key from your Keyboard and hit the Run button.



7. Finally, click the Ok option to remove the leading zeros.



8. The final result after using VBA tools is shown below. This way, you can remove all the leading zeros, giving you the exact number.

Read on to learn how to fix Excel drop-down list not working 

How to Remove Leading Zero From Non-Numeric Values?

Non-numeric value means data containing other than numbers. If you find any data like 00A1011, you can not remove the leading zero from above mentioned-method.

Follow these steps to remove the leading zero from the non-numeric values.

1. First, select the range of cells where you want to remove the leading zero.
2. Insert the formula (=IF(LEFT(D2)="0", RIGHT(D2,LEN(D2)-1),D2)) in the next cell.



3. Press Enter button from the Keyboard. The output is shown here with the first leading zero removed.



4. It is necessary to repeat this formula if your data contains two leading zeros.



5. Here is the final result after removing all leading zeros.

The Bottom Line

Removing leading zeros can help to improve readability and accuracy by portraying the number correctly in Excel.

Furthermore, it can assist in calculating or sorting data by lowering blunders.

If you want to remove the leading zeros from your Excel datasheet, it is best to use the methods described above.