How to Remove Leading Zeros in Excel

There is no gray area regarding leading zeros; you either want them or not at all. When leading zeros aren’t desired, they can make your data look disorganized and difficult to comprehend. With the multiple features Excel offers, you could’ve accidentally configured the settings to display the leading zeros in Excel.

In this article, we will guide you in getting rid of the leading zeros in your Excel spreadsheet using six different methods. Additionally, we will also delve into why your data has leading zeros so you can avoid it the next time.

Why Does My Data Have Leading Zeros?

How to Remove Leading Zeros in Excel?

The solutions to removing leading zeros in Excel depend on how you got them in the first place. Before you move on with the fixes, we suggest you go through the section before this and apply the solutions accordingly.

Use Error Wizard

You could use the error wizard to convert your data to a number if your cell was formatted as text or an apostrophe was used before your number. The error wizard is extremely handy when you’re new to Excel, as it identifies a potential error in data entry. You can convert either one or multiple values into numbers using the wizard. 

Follow these steps to convert your data to number from text using the error wizard:

1. Open your worksheet.
2. Select the cell with the leading zeros.
3. Choose Convert to Number.
   

Use Simple Calculations

As we’ve already mentioned prior to this, Excel will not automatically remove leading zeros if your value is in the text format. A simple workaround for this issue is performing simple mathematical calculations to remove the leading zeros.

Refer to the following steps to convert your value from text to number format through calculations:

1. Open your spreadsheet.
2. On a new cell, enter either one of these calculations:

• =(cell) * 1
  • =(cell) + 0

3. Hit Enter.
4. To convert the remaining cells, drag your cursor from the bottom-right corner of the screen to use flash fill.
   

Change Cell Formatting

If you’ve applied special cell formatting using the Format Cells utility, the error wizard will not register your data as a potential error. In this case, you’ll have to change the cell format from the Home tab. 

To see if any custom formatting has been applied to your cell, select the cell and view the format type in the Number section of the Home tab. If you see the cell formatted as Special, follow these steps to remove the leading zeros:

1. Open your Excel file.
2. Select the cells with the leading zeros.
3. On the Home tab, drop the Special menu down in the Number section.
   
4. Choose General.
   

Use Paste Special

If you’re copying a large number of data to your spreadsheet with leading zeros, you could use the paste special feature to remove all the leading zeros. The Paste Special can be a useful tool when you don’t know if the data you wish to paste is either formatted as text or if any special formatting is applied to it.

Refer to the following steps to use the Paste Special too to remove the leading zeros from your data set:

1. Open your worksheet.
2. Select the data with the leading zeros and hit Ctrl + C to copy the value. 
3. Right-click on a new cell, then choose Paste Special from the list of options.
   
4. On the dialog box, select the box next to Add in the Operation section.
   
5. Click OK.

Text to Column Tool

Although this may sound unconventional, you can also use the Text to Column tool to remove the leading zeros from your number. While using the tool, you will also get the option to change your data format. Using this, you can change the formatting to General to remove the leading zeros from your data.

Here are the steps you can use to remove the leading zeros from the Text to Column Tool from Excel:

1. Launch Excel to open your file.
2. Select your data set, then head to the Data tab from the menu bar.
3. Choose Text to Column.
   
4. In the dialog box, select Delimited, then click Next.
   
5. Under Delimiters, make sure all boxes are unchecked, then select Next.
   
6. Under Column data format, select General.
7. Set the location you wish to enter the new data set.
8. Select Finish.
   

Using Formula

You can use the VALUE function to convert a single text value with leading zeros into a number without the leading zeros. Although you can only use this for one data at a time, you can use flash fill to later convert the other values and remove their leading zeros. If you wish to use the VALUE function to remove the leading zeros from your data set, follow these steps:

1. Open your spreadsheet.
2. In an empty cell, enter the formula in this format:=VALUE(cell)

3. Hit Enter on your keyboard.
4. Place your cursor on the bottom right corner of the cell, then drag it to use flash fill.