How to remove leading zeros in Excel?

Microsoft Excel is a powerful tool for data manipulation and analysis, but sometimes, formatting your data can be a bit tricky. The leading zeros can cause problems in various scenarios, such as when you need to perform calculations or use the data in other applications. Fortunately, Excel provides several methods to efficiently remove leading zeros and ensure your data is clean and ready for analysis.

In this article, we discussed various ways to remove leading zeros in Excel in simple and easy steps.

Also read: How to remove table formatting in Excel?

Convert text to number using error check

It is one of Excel’s easiest methods to remove the leading zeros. If you converted the cells to text by mistake or on purpose for your work but no longer need it that way, then follow the steps below to remove the leading zeros using the error check option:

Step 1: Select the cell, column or row from which you want to remove the leading zeros; a warning sign will appear, and click on it.

Step 2: In the dropdown menu, click the Convert to Number option.

Step 3: All the numbers with leading zeros turned back to normal.

Change custom formatting for cells

Another frequent reason for having leading zeros in your numbers is when your cells consistently display a particular number of digits for each value. Here are the steps to turn it back:

Step 1: Select the cells from which you want to remove the leading zeros, click on the Home tab and then click on the dropdown menu in the Number section.

Step 2: In the dropdown menu, click on the General option.

Step 3: The custom formatting and the leading zeros were removed.

Use Value formula

A simple and fast way to remove leading zeros is by using the value function. This function requires a single input: the text itself or a reference to the cell containing the text. It then returns the numerical value. This method is effective when leading zeros result from an apostrophe or custom number formatting. Here are the steps to use the formula to remove the leading zeros:

Step 1: In our example, we have the values with the leading zeros in the F column; we will use the formula =Value(F2) to remove them. Type the formula and hit enter. You can change the cell number as per your datasheet.

Step 2: You will get the value of the F2 cell with the leading zeros. To apply the same formula in other cells, click and drag the bottom right corner of the box.