# 9 Ways to Remove Leading Zeroes in Excel

Excel is programmed to remove the leading zeroes from the worksheets by nature. But sometimes you may find the leading zeroes in the dataset appearing when you insert the numbers as text or customize the cell formatting. No matter what the reason is, in this article, I will explain to you how you can remove the leading zeroes from Excel. So let’s get into the main part!

In this dataset, the **Raw Data** ranges from cells **A2:A10**. All the cells in column **Raw Data** have **0** at the beginning, yet the number of zeroes is different in all cells. In column **Output**, I will be delivering the final results.

## Case 1: Remove Leading Zeroes in Excel If the Cells Contain Text Values

### Using Error Checking Icon to Remove Leading Zeroes in Excel

When your cell format is set to **Text**, you may find the leading zeroes visible in the cell addresses. The best way to remove them is to use the **Error Checking** command. Here’s how:

- Highlight the cells from
**A2**to**A10**. - Click on the yellow alert icon positioned next to cell
**A2**. - Select the option
**Convert to Number**.

By following these three simple steps, you can effortlessly eliminate the leading zeros that exist in Excel spreadsheets.

### Remove Leading Zeroes in Excel Using the VALUE Function

The **VALUE** function is used to convert text strings into numerical values. Now, follow this step-by-step guide to remove leading zeroes in Excel using the **VALUE** function:

- Type this formula in cell
**B2**:**=VALUE(A2)** - Press
**ENTER**to apply the formula. - Drag the
**Fill Handle**in the cell range**B2:B10**.

The **Fill Handle** will copy the formula from cell **B2** to cell **B10**.

Using the **VALUE** function is also an easy method to remove the leading zeroes.

### Use Flash Fill to Remove Leading Zeroes in Excel

The **Flash Fill** is one of the magic tools of Microsoft Excel. I prefer using **Flash Fill** when I want to avoid using the functions and my output has specific patterns.

To remove leading zeroes in Excel using the **Flash Fill** tool:

- Manually type the first two values without the leading zeroes in a separate column.
- Now, go to the
**Data**tab. - Click on
**Flash Fill**from the**Data Tools**group.

The **Flash Fill** command normally does the job perfectly. But I will recommend that you skim through the answers after applying the **Flash Fill**.

### Multiplying the Numbers with 1 to Remove Leading Zeroes in Excel

Another technique to remove the leading zeroes is to multiply the **Raw Data** by **1** in an empty column. Here are the steps:

- In cell
**B2**, insert this formula:**=A2*1**

- Press
**ENTER**. - Drag the
**Fill Handle**from cell range**B2:B10**.

The formula vanished the leading zeroes from the text strings.

### Remove Leading Zeroes in Excel Using the Paste Special Command

Another way is to multiply an empty cell with the **Raw Data** to remove the zeroes. If you apply this method, you can get the results in its cell address, **A2:A10**.

Follow the steps below to remove the leading zeroes in Excel using the Paste Special command:

- Select an empty cell.
- Press the
**CTRL+C**key to copy the content of it. - Select the cell range
**A2:A10**. - Right-click on the cell selection to open the
**context menu**. - Select
**Paste Special**to open the**Paste Special**dialog box. - Select the
**Add**radio button from the**Operation Section**. - Hit the
**OK**button.

The values removed the leading zeroes from their text strings in cell **A2:A10**.

### Using Text to Columns Option to Remove Leading Zeroes in Excel

Here’s a step-by-step guide on using the **Text to Columns** option in Excel to seamlessly remove leading zeroes and ensure your data is correctly formatted:

- Select the cell range
**A2:A10**. - Go to the
**Data**tab. - Select the
**Text to Columns**command from the**Data Tools**group. - Select the
**Delimited**radio button in the**Convert Text to Columns Wizard**. - Click on
**Next**. - Unmark all the
**Delimiters**. - Click on
**Next**. - Select the
**General**button from**Column Data Format**. - In the
**Destination**box, type**B2**or**$B$2**and hit**Finish**.

The results will appear in cell **B2**, just like in the image below:

### Using VBA to Remove Leading Zeroes in Excel

You can use **VBA (Visual Basic for Applications)** to remove leading zeroes in Excel. Here’s the VBA code for removing the leading zeroes in Excel:

```
Sub RemoveLeadingZeros()
Dim rng As Range
Dim cell As Range
Dim strValue As String
' Set the range where you want to remove leading zeros
Set rng = Range("A1:A100") ' Change the range to your desired range
For Each cell In rng
If cell.Value <> "" Then
' Remove leading zeros from the cell value
strValue = CStr(cell.Value)
strValue = Application.Trim(strValue)
cell.Value = strValue
End If
Next cell
End Sub
```

To remove the leading zeroes in Excel using VBA:

- Press
**ALT + F11**to open the**Visual Basic Editor**. - Then select
**Insert > Module**to open a new module. - Copy the
**VBA**code from above and paste it into the new module. - Close the
**VBA**editor by clicking the close button. - Go to the
**Developer**tab. - Select
**Macro**from the**Code**group. - From the
**Macros window,**select the**RemoveLeadingZeros**macro from the list. - Click on the
**Run**button. - Press
**ALT + F8**to run the**Macro**.

For this dataset, I am using **Range(“A2:A10”)** as the range in the code where I want to remove leading zeroes. You can change it to your desired range, such as **“A1:A50”** or **“B2:B200”**, etc.

If you are comfortable with using **VBA** in Excel, you may find this method the easiest of all. The result is in the following image:

**Shortcut keys to close VBA Editor: ALT + Q**

## Case 2: Remove Leading Zeros Cells Contain Numeric Values in Excel

A rare case is when the** Number Format** is changed to a **Special Number Format**. Even though leading-up zeros are usually visible because they are in text format, this does happen occasionally.

Follow these steps to remove the leading zeroes in Excel:

- Select cell range
**A2:A10**. - Go to the
**Home**tab. - Click on the
**Format Cells**drop-down menu from the**Number**group. - Select
**More Number Formats**to open the**Format Cells**dialog box. - Go to
**Custom**. - Remove the zeros from the
**Custom Type**and keep an empty box and click**OK**.

You must make sure that your cell format is **Special Number Format** before using this procedure. Otherwise, this technique won’t be effective.

## Case 3: Remove Leading Zeros When the Cells Contain Both Numeric and Non-numeric Values

This method is especially useful for removing leading zeroes from text strings that contain both numeric and non-numeric values in the cell addresses. However, you can also use this approach to remove numeric values.

**Formula**

**=RIGHT(A2,LEN(A2)-FIND(LEFT(SUBSTITUTE(A2,"0",""),1),A2)+1)**

**Formula Explanation**

**SUBSTITUTE(A2, “0”, “”)**replaced all occurrences of the character**“0”**in the text in cell**A2**with an empty string**“”**.**LEFT(SUBSTITUTE(A2, “0”, “”), 1)**extracted the first character from the result obtained in**SUBSTITUTE(A2, “0”, “”)**.**FIND(LEFT(SUBSTITUTE(A2, “0”, “”), 1), A2)**located the position of the first non-zero character (obtained in**LEFT(SUBSTITUTE(A2, “0”, “”), 1)**). It returns the position of the first occurrence of the non-zero character.**LEN(A2) – FIND(LEFT(SUBSTITUTE(A2, “0”, “”), 1), A2) + 1**calculated the number of characters from the first non-zero character to the end of cell**A2**. It does so by subtracting the position of the first non-zero character from the total length of**A2**and adding**1**. The**+1**is added to include the first non-zero character itself in the count.**RIGHT(A2, LEN(A2) – FIND(LEFT(SUBSTITUTE(A2, “0”, “”), 1), A2) + 1)**extracted a substring from cell**A2**.

To remove the leading zeroes when cells contain both numeric and non-numeric values:

- Select a cell.
- Now, use this formula:
**=RIGHT(A2,LEN(A2)-FIND(LEFT(SUBSTITUTE(A2,”0″,””),1),A2)+1)** - Drag down the
**Fill Handle**from cell**B2**to**B10**.

Now, you have the values with the leading zeros removed in **Output (****column B)**.

## Conclusion

In this article, I have discussed nine methods to remove the leading zeroes from text strings in Excel. I hope you’ve found this article quite helpful. If you have any questions regarding this topic, please let me know in the comment section below.

## Frequently Asked Questions

### Why do I have leading zeroes in my Excel cells?

Leading zeroes often appear when dealing with numbers that are formatted as text or when importing data from external sources. They can affect calculations and sorting.

### Can I remove leading zeroes for a specific range of cells?

Yes, you can. Simply select the range of cells containing the leading zeroes and apply the steps mentioned in the article to remove them.

### Will removing leading zeroes affect my numeric data?

No, removing leading zeroes only affects the display format of the numbers. The actual numeric values remain unchanged.

### Is there a way to automate the removal of leading zeroes in Excel?

Yes, you can use formulas or Excel functions like VALUE or TEXT to convert text-formatted numbers with leading zeroes into numeric values.

### Can I undo the removal of leading zeroes if needed?

Yes, you can always use the **“Undo”** feature in Excel **(Ctrl + Z)** immediately after making changes to revert the removal of leading zeroes.

### What problems can leading zeroes cause in Excel?

Leading zeroes in numeric data can lead to issues with calculations and sorting. For example, a number like “00123” might be treated as smaller than “0123” in a sort operation.

### Are there any scenarios where I should keep leading zeroes?

Leading zeroes are often necessary when dealing with codes, such as product IDs or ZIP codes, where the length of the code is significant. In such cases, removing leading zeroes may impact data integrity.

### How can I prevent leading zeroes from appearing in Excel in the first place?

When entering data, ensure that you are using the correct cell format (General or Number) to avoid leading zeroes. Additionally, consider using custom formats to control how numbers are displayed.