How to Remove Zeros in Front of a Number in Excel (6 Easy Ways)

Method 1 – Convert Numbers in Text Format to Number Format to Remove Zeros in Excel

1.1 Using Error Checking Option to Remove Zeros in Front of a Number

Steps:

Copy and paste the numbers with leading zeros in the Output column.
Select all the cells of the output column.
It will show an error icon on the cells. Click on it.

Select the option named Convert to Number.

All zeros in front of the numbers are removed.

1.2 Change Custom Format to Number to Remove Zeros in Front of a Number

Steps:

Select all the cells of the Output column.
Go to the Home tab.
Click on the cell Format options and select the Number format.

Leading zeros are removed and cells are in Number format. But there will be some decimal digits after the number.

If you want to remove the decimal zeros after the number then select the cells of the Output column and go to the Number format option.
Click on the Decrease Decimal button as many times as the number of decimals you want to remove.

The numbers will not have any leading and trailing zeros now.

Method 2 – Use Excel Formulas to Remove Zeros in Front of a Number

2.1 Using VALUE Function

Steps:

Paste this formula into cell C5.

=VALUE(B5)

Drag the Fill Handle icon to paste the used formula to the other cells of the column or use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste.

2.2 Combining VALUE and TEXT Functions to Remove Zeros before a Number

Steps:

Paste this formula into cell C5

=TEXT(VALUE(B5), "#")

Paste the same formula to the other cells of the output column.

Method 3 – Multiply Numbers with 1 to Remove Zeros in Front of a Number in Excel

Steps:

Enter this formula in cell C5:

=B5*1

Use the Fill handle icon to paste the same formula to the other cells.

Method 4 – Use Excel Paste Special Command to Remove Zeros before a Number

Steps:

Paste the numbers with leading zeros in the output column.
Insert 1 in any cells outside of the dataset table.

Copy the cell which contains 1.
Select all the cells of the Output column and right-click on the selected cells to open the Options.

Select the Paste Special option.

A new window will appear named “Paste Special”.
Select “All” in the Paste options
Select the Multiply option and press OK.

All zeros in front of the numbers are removed.

Method 5 – Use Excel’s Text to Columns Wizard to Remove Zeros in Front of a Number

Steps:

Select the cells in the Number column.
Go to the Data tab and select the “Text to Column” option

In the Step 1 window, select the “Delimited” option and press the Next button.

In the step 2 window, just press the Next button.

In the last window, select the C5 cell in the last window. Press the Finish button.

All zeros in front of the numbers are removed.

Method 6 – Applying VBA Code to Delete Zeros in Front of a Number in Excel

Steps:

Go to the top ribbon and press on Developer, then press on the Visual Basic option from the menu.

You can use ALT + F11 to open the ‘Microsoft Visual Basic for Applications’ window if you don’t have the Developer tab added.

Now, a window named “Microsoft Visual Basic for Applications” will appear. From the top menu bar, press on “Insert” and select the “Module’” option.

Now, a new “Module” window will appear. And Paste this VBA code into the box.

Sub Delete_front_Zeros_in_numbers()
Dim Delete_Range As Range
Dim Work_Range As Range
On Error Resume Next
xTitleId = "Delete Zeros in front of Numbers"
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

To run the code go to the top menu, press on the Run option and select the Run Sub/UserForm. You can press F5 to run the code.

After clicking on the Run button, it will ask you to select the cell range where you want to remove the the leading zeros.
Select the desired cell range of (eg., C5:C11) from the worksheet and press OK.

All leading zeros will be removed.

How to Remove Specified Number of Zeros in Front of Numbers in Text Format

All the methods shown above will remove all of the leading zeros of a number. But you may sometimes need to remove a specific number of zeros from the front.

Steps:

We will remove the leftmost 1 zero from the number. And if there are no zeros in front it will keep the number the same.
For this, paste this formula into cell C5

=IF(LEFT(B5,1) = "0", RIGHT(B5, LEN(B5)-1), B5)

Formula Breakdown:

LEFT(B5,1) = “0” : this is the condition of the IF function. It says the condition is when the leftmost character is 0. The LEFT function extracts the leftmost character of the cell.
RIGHT(B5, LEN(B5)-1): It is the task if the condition is satisfied. The LEN function will count the total characters of cell B5. And the RIGHT function will exclude the first character by taking the rightmost (Total-1) characters
B5: If the condition doesn’t meet then it will give back the cell value unchanged.