Excel, by default, automatically removes leading zeroes whenever you enter numbers. So, you might be wondering why there are leading zeroes in the Excel file you received.

Well, there are two possible reasons for that. It could be because:

- The cell is in text format.

This can happen if there’s an apostrophe at the start of the cell. Apostrophe (‘) is a special character that tells Excel to treat a cell as a text. If you are working on an Excel file exported from a system, there’s a great chance you’ll encounter this.

Remember that adding an apostrophe is just one way of converting a cell into a text. A cell can also be converted into text by simply changing its number format – so check on that too.

- The cell is formatted to have a fixed number of digits.

If you have a 3-digit number entered in a cell that was formatted to always have 5 digits, Excel will automatically fill in the missing 2 digits with leading zeroes.

There are plenty of ways to remove the leading zeroes in Excel. They vary depending on the reason for having them. I’ve arranged the methods below to tackle first the most common causes. I suggest you go through each of them and see what works on your data.

## Error Check to Remove Leading Zeroes in Excel

Excel will typically warn you if you have cells that contain numeric values but a formatted as text. You will notice this when you see small green triangles added on the left side of these cells (as shown below).

If you don’t see them, it’s most likely that *Background Error Checking* is disabled on your Excel.

To enable it, simply go to *File >> More >> Options*.

In the **Excel Options **menu, select Formulas.

Go to the *Error Checking *section, tick the **“Enable background error checking” **checkbox, and click OK.

You should now see the small green triangles on the left side of your cells.

(If you don’t see them still, please proceed to the method that follows after this).

Select all these cells, and a yellow warning icon will crop up on the first cell selected.

Click on that icon and select **Convert to Number**.

And that’s it! All leading zeroes should now disappear.

With this approach, we have taken advantage of Excel’s default setting of removing leading zeroes from numbers.

## Changing the Number Format to Remove Leading Zeroes in Excel

If the previous method didn’t work for you, it might be because your cells are in a “Special” format where leading zeroes are automatically added so that it reaches a particular number of digits.

To change this:

1. Select all the cells with leading zeroes.

2. Go to the **Home **tab. Look at the current number formatting of these cells from the Number Section. If it is “Special”, change the number formatting to General (or any other number formatting appropriate for your data).

And that’s it! You should no longer see any leading zeroes in your selected cells.

## Using “Paste Special” to Remove Leading Zeroes in Excel

If you don’t want to alter the cells containing leading zeroes and would only want the numbers on a different column, then you can do the following:

1. Select a blank cell on the worksheet and press **CTRL + C** to copy it.

2. Next, select all the cells with leading zeroes.

3. Then, press **CTRL + ALT + V** to open the *Paste Special* menu.

Select **Add **under the *Operation *section and click OK.

And that’s it! You should now have removed all the leading zeroes from your cells.

This method worked because it added a zero (or a blank cell) to all the cells with leading zeroes, which in the process, converted the cells into numbers.

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

Another way to remove leading zeroes without altering the original column is to use the VALUE() function.

**VALUE()** is a text function in Excel that converts a text string that looks like a number into an actual number.

On a new column, type the following formula: **=VALUE([range])**

As you press enter, the leading zeroes will disappear.

Copy this formula to the remaining rows, and voila! No more leading zeroes for the entire column.

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

If you have a lot of rows to remove the leading zeroes from, this may be the best option for you.

1. Highlight the entire column that contains leading zeroes.

2. Go to the **Data **tab. From the *Data Tools* section, click **Text to Columns**.

3. The **Convert Text to Columns Wizard **will appear.

Select **Delimited** and click Next.

4. Uncheck all the *Delimiters checkboxes* and click Next.

5. In the Column Data Format, select **General.**

Notice there’s a note in the Wizard that says:

'General' converts numeric values to numbers, date values to dates, and all remaining values to text.

This is the Text to Column feature that we are making use of.

Click **Finish** to close the Wizard.

You should now see all the leading zeroes removed from your selected column.

If you have used Text to Columns before and worry that this may affect other columns alongside the current field, don’t worry. Only the selected column gets updated with the settings we selected in the Wizard.

## Remove Leading Zeroes from an Alphanumeric Data

The methods described above involved the conversion of the cell into a number format.

If you are working on data with alphanumeric characters (a combination of both letters and numbers) and you only want to remove the leading zeroes, then this next method is for you.

1. On a blank column, type this formula:

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

Change all instances of “A2” with the range where your alphanumeric data is.

2. Once you click enter, the cell should result in an alphanumeric character with no leading zeroes.

3. Copy this formula to the remaining rows in your cells, and you’re all set up.

## Conclusion

Leading zeroes are often intentionally added for a particular purpose. However, there are instances when we need to remove them. I hope the methods provided above have helped you do so with ease.