Osman Goni Ridwan
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.
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.
- 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
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.
- 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.
- 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.
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)
- Now, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column or use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste.
Similar Readings
- How to Hide Zero Values in Excel Pie Chart (3 Simple Methods)
- Ignore Blank Cells in Excel Bar Chart (4 Easy Methods)
- How to Ignore Blank Series in Legend of Excel Chart
- Excel IFERROR Function to Return Blank Instead of 0
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.
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.
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.
- 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.
Similar Readings
- Apply VLOOKUP to Return Blank Instead of 0 or NA
- Hide Chart Series with No Data in Excel (4 Easy Methods)
- How to Hide Zero Values in Excel Pivot Table (3 Easy Methods)
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
- 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. And press the Finish button.
- As a result, all zeros in front of the numbers are removed.
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.
- 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.
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.
- 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. And in the last column, all leading zeros are removed.
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.
