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.


Download Practice Workbook

You can download the practice workbook from here:


6 Methods to Remove Zeros in Front of a Number in Excel

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.

How to Remove Zeros in Front of a Number in Excel


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

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

In general, the numbers are converted to text format by adding an apostrophe in front of them. But there will show 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.

How to Remove Zeros in Front of a Number in Excel

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

How to Remove Zeros in Front of a Number in Excel

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

How to Remove Zeros in Front of a Number in Excel


1.2 Change Custom Format to Number

Again, sometimes, the numbers in the cell are custom formatted to certain digits. So, to make the number of that 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.

How to Remove Zeros in Front of a Number in Excel

  • As a result, you will see the leading zeros are removed and cells are in Number format. But there will create some decimal digits after the number.

How to Remove Zeros in Front of a Number in Excel

  • 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.

How to Remove Zeros in Front of a Number in Excel

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

How to Remove Zeros in Front of a Number in Excel

Read More: How to Exclude Zero Values with Formula in Excel (3 Easy Ways)


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)

How to Remove Zeros in Front of a Number in Excel

Using VALUE Function


Similar Readings


2.2 Combining VALUE and TEXT Functions

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.

Using VALUE and TEXT Functions


3. Multiply Numbers with 1

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 similar formula to the other cells.

Multiplication of the Column with 1


4. Use Paste Special Command

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.

Using Paste Special Command

  • 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.

Using Paste Special Command

  • Here, select the Paste Special option.

Using Paste Special Command

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

Using Paste Special Command

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

How to Remove Zeros in Front of a Number in Excel


Similar Readings


5. Use Text to Columns Wizard

You will find another great feature in Excel that 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

Using Text to Columns Feature

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

Using Text to Columns Feature

  • In the step 2 window, you have to do nothing, just press the Next button.

Using Text to Columns Feature

  • In the last window, select the C5 cell in the last window. And press the Finish button.

Using Text to Columns Feature

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

Using Text to Columns Feature


6. Use a VBA Code

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.

Using Excel VBA Macro

  • 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.

Using Excel VBA Macro

  • 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

Using Excel VBA Macro

  • 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.

Using Excel VBA Macro

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

Using Excel VBA Macro

Read More: How to Hide Rows with Zero Values in Excel Using Macro (3 Ways)


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.

Remove Specified Number of Zeros in front of Numbers in Text Format

  • 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.

Remove Specified Number of Zeros in front of Numbers in Text Format

  • 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.

Remove Specified Number of Zeros in front of Numbers in Text Format

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

How to Remove Zeros in Front of a Number in Excel


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 ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo