Multiple Ways to Remove Leading and Trailing Zeros

In this article, we’ll learn how to remove the leading and trailing zeros in Excel.

Excel Removes Leading and Trailing Zeros Automatically

Leading zeros

Sometimes when you type numbers in excel, like phone numbers, credit card numbers, etc, excel automatically removes the zero which you put at the start(lead). For example- if you enter 005600 in a cell in excel, it would automatically convert it into 5600.

Trailing zeros

When you use decimals in excel, it automatically removes the zeros after the decimal point. For example- If you write 0.880 in a cell of excel and press enter, then it will automatically remove the trailing zero and show 0.88.

Methods to remove Leading Zero in Excel

You may want to retain the leading zeros or remove them based on your requirements. Let’s see some of the reasons why you would retain the leading zeros and then learn how to remove leading zeros in Excel.

Excel is set up such that it automatically removes the leading zeros. But there are some instances in which you have to remove the zeros by yourself:

In case the numeric value in excel gets formatted i.e change in the appearance of numbers, including dates, times, mostly apostrophe before the number, without changing its actual value, it retains the leading zeros.
Sometimes, the cells of excel are formatted in such a way that it has a particular amount of numbers in the cell.
For example: if a cell is set to have 4 digits and you put a value having less than 4 digits then excel includes one leading zero to nullify it.

Hence, the methods by which we will remove leading zeros depend on the reasons for presence.

Let’s now learn to remove the leading zeros in Excel.

1. Checking Option Changing The Texts To Numbers Using Error

If the reason for having leading zero is that you’ve added apostrophes before the numbers, you can use the error checking method to convert these back into numbers with a single click.

Example: here is a data set where I have put some numbers having apostrophes as well as leading zeros.

To remove these zeros following steps are to be followed –

Select the particular cell from which you want to remove leading zeros. You will notice that there is a yellow icon at the top right part after selection.

Click on the yellow icon, it generally denotes an error

Click on “convert to numbers” and it’s done. Excel automatically removes the leading zeros.

Remove leading zero in Excel — Final Output

And as excel is programmed to remove leading spaces from any number by default, doing this would automatically remove leading zeros and spaces too.

2. Convert the Custom Number Formatting of the cells

The second common reason for leading zeros may be the custom numbers setup of the cell. Generally, in order to make the cells look ordered and of the same length, you may have kept a “0” padding.

So if a number is 3 digit but you want all cells to contain 5 digits, Excel will automatically “pad” the number with two leading zeros.

Here I have a dataset where the custom formatting has been applied to always show a minimum of six digits in the cell.

Following are the steps to resolve this:–

Select the cells in which the numbers with leading zeros.
Go to the home tab.
There’s a number group, click on the number format dropdown.

Select ‘General’

General Number Format — Selecting General

Thus, this would change the custom number formatting of the cells and numbers will be displayed without leading zeros.

3. Multiplying by 1 [Paste Special Technique]

This specific can be used to solve both the problems mentioned i.e where numbers have been converted into text by using an apostrophe or a custom number formatting has been applied to the cells.

Example: Let’s consider a data set like this and you want to remove the leading zeros from this.

Following are the steps to be followed: –

Copy any empty cell from that worksheet.
Select the cells having leading zeros and from which you want to remove them.
Right-click on the selection and then click on Paste Special. This will open the Paste Special dialogue box.

Special Paste Foramat in Excel — Special Paste Foramat

Click the ‘add’ option like the following.

Add operation in Excel in Paste Format — Add operation

Click ‘ok’

By this, the leading zeros get removed.

This method only helps in changing numbers, if there are any texts then it remains unchanged.

Methods to Remove Trailing Zeros

Trailing zeros in excel is generally when a decimal is used and due to some discrepancy excel fails to remove them.

1. Using Decimal Numbers by Format Cells

First you have to select the cell in which you want to remove trailing zeros after the decimal point, right-click to the Format Cells from the context menu.

Changing Cell Formatting in Excel — Cell Formatting

In the format cell dialog, choose the ‘custom’ from the category list box.

Then type 0.### into the type textbox in right section.

removing trailing zeros 3 — Entering Type

Click ‘ok’.

Thus, the trailing zeros are removed.

2. Removing Same Number of trailing zeros by Formula

In case you want to remove the same numbers of zeros from the end, you can apply a formula to solve it.

Select the adjacent cell to the number you used.
Type this formula =LEFT(D1, LEN(D4)-2)*1, D4 is the cell you will remove trailing zeros from, 2 is the number of zeros you want to remove.
Then drag the fill handle over the cells to apply this formula.

Conclusion

Excel generally removes the unwanted zeros automatically from the starting(leading) and ending(trailing) unless some special cases as mentioned above. The presence of leading and trailing zeros is sometimes really annoying and hence these are the methods with which you can remove them at ease. Stay tuned to Quick Excel for more such Tutorials.