How to remove leading zeros in excel

It is very frustrating to see that Microsoft Excel has appended some zeros before the number that you entered into a cell. This usually does not happen by default.

You may find yourself in such a situation if the Excel spreadsheet you are working on was created by someone else who added the leading zeros to the number.

Better still, you can have a workbook that has been formatted to add a certain number of zeros in front of any number that is typed.

If you are not aware then this could be very frustrating when you try to get rid of it.

For example, if you work with telephone numbers, student IDs, employee IDs or anything that involves a database with numbers, you may need to remove the leading zeros in such a case. 

Microsoft Excel has been programmed to automatically remove any leading zeros from the numbers. It applies the General or Number format to the numbers thereby automatically removing preceding zeros. 

However, Microsoft Excel also offers different ways to add or  remove the leading zeros. To be able to accurately remove leading zeros from a spreadsheet, you will have to know exactly how the leading zeros were created or added to the numbers.

In this article, you will be shown the various methods to find out how the leading zeros were added and how the methods you can use to remove leading zeros in excel and they include:

1. Removing leading zeros with the custom number format method
2. Removing leading zeros by converting text to numbers
3. Removing leading zeros using the Value() function

Method 1: Removing leading zeros with the custom number format method

Custom number format is just a formatting style that can be applied to any cell in Excel. This allows you to set how you want the values in a particular cell to appear. Hence, one can use this to set the numbers in the various cells to have zeros before them.

Hence, before you use this particular method, you must first check to see if the custom number format  has already been applied. 

How to know if the leading zeros in your Excel spreadsheet is from a custom number format

Once you have a spreadsheet with leading zeros in just as is shown in the image below, click within any of the cells with the leading zeros.

Upon clicking within any of the cells with the leading zeros, pay attention to the formula bar. If you find out that the number written in the formula bar does not have the leading zeros then you know that the leading zeros are as a result of a custom format set.

This means that the custom format when applied only affects the appearance of the number and not the value itself. Now let’s take a look at how to remove the leading zeros from these cells.

Steps to remove leading zeros using custom format method

Below are the steps on how to remove leading zeros using custom number format method

1. Select the cells that have the numbers with the leading zeros

2. Make sure you are on the home tab or click the Home tab if you are not already there

3. In the Numbers group, click on the Number Format dropdown

4. Select ‘General’ to convert the numbers back to their normal form without the leading zeros

Using the steps above will change the custom number formatting of the cells and the leading zeros will disappear. Note that this technique would only work when the reason for the leading zeroes was custom number formatting.

Method 2: How to remove leading zeros by changing from Text format to number

Another way that numbers can be formatted in Excel is formatting as text. This may be applied for several different reasons. When numbers are formatted as text, they will always show the numbers the way they were typed. 

Hence, if you mistakenly type some zeros in front of a number while the cell is formatted as a text, it will show the leading zeros.

As such, if you find some leading zeros in your spreadsheet, then those specific cells have been formatted to text instead of numbers.

How then can you tell if a cell has been formatted as a text and how can you change such formatting. Well, let’s take a look at how to do this.

How to tell if a cell has been formatted as a text and not numbers

There are three easy ways to identify things in Excel that will notify you that the formatting for a particular cell is text and not numbers.

Once you have numbers in a cell which have been formatted as a text;

• You will find a small green right angle triangle at the top left corner of the cell

• The numbers are aligned to the left. By default, Excel aligns numbers to the right and text to the left, so one of the quickest ways to see that a number has been formatted as text is to check the alignment of the number within the cell.

• You will find a small yellow exclamation symbol just beside the cell when you click within it. So, to find out if a cell in Excel containing numbers is formatted as text, click within it and you will see the yellow symbol beside the cell.

When you hover your mouse over that yellow symbol you will see a tooltip that states that “The number in this cell is formatted as text or preceded by an apostrophe”

How to change text formatting to numbers in order to remove leading zeros

This is the most convenient way of removing leading zeros in excel. To apply this method, follow the steps below.

1. First select the cells that are showing numbers with leading zeros 

2. Click on the little yellow icon that appears when you select the cell range.

The image below shows exactly what you will see when you click on the yellow icon

3. On the drop-down menu that appears, look for the option “Convert to Number” and click on it to convert all the text to numbers to remove the leading zeros.

4. All the cells selected will be converted to numbers with no leading zeros

NB: Another way you can change the formatting from text to numbers is to remove any Apostrophe that comes before the number if you see some in the formula bar when you click within a particular cell.

Method 3: Removing leading zeros using the Value() function

Sometimes, some people add leading zeros to numbers through the use of various formulas. Just as I mentioned earlier, to be able to remove leading zeros from Excel accurately and easily, you would have to know how they were added.

With the cells that have leading zeros added from a function, you can use a function in Excel called Value() function to retrieve the number itself without the leading zeros.

Let me illustrate how one could have used a formula to add leading zeros to a number. By using the formula TEXT(), one can convert any value including numbers into text in a specified format. So, if I want any value you put into a cell to have 8 digits and possibly lead with zeros when the value entered isn’t up to eight, then I can use the formula below on the specific cell.

=TEXT(A1, “00000000”)

The formula above will convert any value in cell A1 to have eight digits. So, if A1 has a value of 22, then the outcome of the formula above will be 00000022

So, if this was the method used to generate the leading zeros or something similar then we can use the Value() function to remove the leading zeros.

To use the Value() function to achieve such results, then you have to use a different column to receive the values and still keep the original column.

In this example, let’s generate the values without the leading zeros to column B. This means that for each cell in column A, we will have its actual value without the leading zero in column B.

To do this, let’s focus on the first cell (A1) for now.

Click within the cell B1 because that’s where we want to print the actual value of cell A1 without the leading zeros. Then type the formula below into the formula bar: =VALUE(A1) then click enter afterwards.

Upon clicking enter, you will get the actual value without the zeros. The next thing to do is to apply this formula to all the other relevant cells. You can easily do this by selecting cell B1 and dragging the pointer at the lower right corner to select the remaining cells. This will automatically apply the formula to all the other cells.

Here is the outcome of the above steps.

After going through the various methods above, if you still are not able to remove the leading zeros in your spreadsheet then it probably means that the formula that was used to generate the leading zeros is different from what we have illustrated here. 

As such, let us take a look at how you can remove leading zeros when you have no idea which formula was used to create the leading zeros.

Is a simple logic that you can apply to get the numbers without the leading zeros. The logic is that when you do a multiplication in Excel, the product you get will be formatted as a number without leading zeros. This means that I can take a cell with a number like 00054 and multiply it by 1 and get the result to be 54 (without the leading zeros).

You can therefore apply this logic to any column and get your numbers without the leading zeros. Let’s see the steps to take to achieve this logic.

Create one more column and let the second column hold the product for the first column and the number one as explained below.

Let’s start with the first cell (B1)  then we can apply the result to the other cells in the B column. Click on B1 and type the formula below into the formula bar: =A1*1 

Click enter after typing the formula and you will get the value of cell A1 but this time without the leading zeros.

Click on cell B1 and drag it across the remaining cells to apply the formula to all of them and get similar results.