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.