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

If you are searching for the solution or some special tricks to remove zeros in front of a number in Excel then you have landed in the right place. There are many ways to remove zeros in front of a number in Excel. This article will show you each step with proper illustrations so, you can easily apply them for your purpose. Let’s get into the main part of the article.

In this section, I will show you 6 quick and easy methods to remove zeros in front of a number in Excel on the Windows operating system. You will find detailed explanations of methods and formulas here. I have used Microsoft 365 version here. But you can use any other versions as of your availability. If any methods don’t work in your version then leave us a comment.

1. Convert Numbers in Text Format to Number Format to Remove Zeros in Excel

By default, numbers in Excel don’t contain 0’s in front of them. You will see zeros in front of numbers if the cells are in Text format. You can convert the text format to numbers in many ways. I am showing 2 easy ways of them.

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

In general, the numbers are converted to text format by adding an apostrophe in front of them. But there will be an error on them. You just go to the error option and convert them to numbers. I am showing the procedure step by step below-

📌 Steps:

At first, copy and paste the numbers with leading zeros in the Output column.
Then, select all the cells of the output column.
Here, you will see an error icon on the cells. Just click on it.

Here, you will find an option named Convert to Number. And, select it.

You will see all zeros in front of the numbers are removed.

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

Again, sometimes, the numbers in the cell are custom-formatted to certain digits. So, to make the number of those digits, they add zeros in front. In this case, to remove the zeros, you have to change the format of the cells.

📌 Steps:

First, select all the cells of the Output column.
Then, go to the Home tab.
Click on the cell Format options and select the Number format.

As a result, you will see the 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 again.
Click on the Decrease Decimal button as many times as the number of decimals you want to remove.

Finally, you have numbers without any leading and trailing zeros.

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

Excel has some functions that you can use to remove the leading zeros of a number. Here, I am showing 2 ways to use formulas.

2.1 Using VALUE Function

You can use the VALUE function for this task. Follow the shown steps for this-

📌 Steps:

First, paste this formula into cell C5.

=VALUE(B5)

Now, 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

You can use the TEXT function with the VALUE function for this task. It will remain the numbers cells in the TEXT format. Follow the shown steps for this-

📌 Steps:

First, paste this formula into the cell C5

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

Then, paste the same formula to the other cells of the output column.

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

There is an interesting trick in Excel to get rid of zeros in front of numbers. You have to just multiply them by 1 and you see all the leading zeros are gone away. Follow the steps below to have a better idea.

📌 Steps:

First, write this formula in the cell C5:

=B5*1

Then, use the Fill handle icon to paste a similar formula to the other cells.

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

You can also the Paste Special feature to multiply the cells with leading zeros by 1. Follow the steps below-

📌 Steps:

First, paste the numbers with leading zeros in the output column.
Then, insert 1 in any cells out of the dataset table.

Then, copy the cell which contains 1.
Now, select all the cells of the Output column and press the right-click of the mouse on the selected cells to open the Options.

Here, select the Paste Special option.

Now, a new window will appear named “Paste Special”.
Select “All” in the Paste options
Then, select the Multiply option and press OK.

As a result, all zeros in front of the numbers are removed.

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

You will find another great feature in Excel which is Text to Column. Follow these steps to use this feature for the task.

📌 Steps:

Select the cells in the Number column.
Then, 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, you have to do nothing, just press the Next button.

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

As a result, all zeros in front of the numbers are removed.

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

I am sharing a VBA macro code to delete the leading zeros in front of numbers in Excel. Follow these steps to apply and run the macro code.

📌 Steps:

For this, first, go to the top ribbon and press on the 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. Here from the top menu bar, press on the “Insert” And a menu will appear. From them 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 here will open some other options and select the Run Sub/UserForm also you can simply press F5 to run the code.

After clicking on the Run button, it will ask you to select the cell range from where the leading zeros will be removed.
Select the cell range of C5:C11 from the worksheet and press OK.

And, you will see the numbers are free of zeros in front.

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

All the methods shown before 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. Here, I will show the procedure to remove leading zeros one by one.

📌 Steps:

First, 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 number 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.

Then, drag the Fill Handle icon to the right 3 cells. So, it will apply a similar formula to these cells. As a result, it will remove one zero in front of the numbers each time applying the formula.

Then, select the cells C5:F5 and drag the Fill Handle icon to the last row of the dataset to apply a similar formula to the cells.

As a result, you will see the leading zeros are removed one by one where possible. In the last column, all leading zeros are removed.

Download Practice Workbook

You can download the practice workbook from here:

Remove Leading Zeros.xlsm

Conclusion

In this article, you have found how to remove zeros in front of a number in Excel. I hope you found this article helpful. You can visit our website to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back to Pad Zeros in Excel | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

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

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

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

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

2.1 Using VALUE Function

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

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

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

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

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

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

Conclusion

Related Articles

Leave a reply Cancel reply