Removing Leading Zeros in ExcelWhy they appear and how to deal with them
Whenever you type any number that has leading zeros in a cell in Excel, it will appear without those leading zeros by default.
This is because Excel ignores any leading zeros of any data that is typed as a number.
For example, if you type 000178912, it will appear as 178912 by default.
So if you’re seeing cells that have numbers with leading zeros anywhere on the Excel sheet that you’re working on, you’re probably working on an Excel sheet that someone else worked on.
You’re most likely not working on an Excel sheet that you created.
Or it could be that you selected a layout that allows it to happen.
Either way, these leading zeros appearing may not comply with your preferred formatting. As such, you may want to remove them.
Fortunately, removing these leading zeros isn’t that complicated at all.
There’s even an efficient solution when you’re dealing with a large amount of data.
So whether you’re dealing with a single cell or several cells, there’s a solution for you.
But before we discuss the solutions, let’s go over the possible reasons why leading zeros are appearing on your Excel sheet.
Why Are Leading Zeros Appearing?
By default, a number that has leading zeros will appear without the leading zeros in Excel (if it’s typed as a number).
So if there are any leading zeros appearing on cells that have numbers, it’s probably because of the following possible reasons:
- The number is formatted as text (usually done by adding an apostrophe (‘) at the beginning of the number). You’ll also notice that the cell is aligned to the left
- It could be due to custom formatting where cells show numbers at a certain length. For example, it may be formatted to always show 6 digits. Any number that has less than 6 digits will appear with leading zeros (e.g. 123 will appear as 000123)
- They could be added by a formula (e.g. using the TEXT function)
Remove Leading Zeros That Appear On Cells Formatted as Text
When you input a number in a cell that is formatted as text, it will appear as a text string.
As such, any leading zero will appear on the cell since it’s not treated as a number.
Another way for a number to appear as a text string is when there’s an apostrophe at the beginning.
When there’s an apostrophe at the beginning of any value, Excel will treat it as a text string.
As such, any leading zeros on a number that has an apostrophe at the beginning will appear on the cell.
This is because Excel doesn’t treat is a number.
You’ll also notice that despite being a number, the cell is aligned to the left rather than the right.
And most of the time, you’ll see an alert symbol next to the cell.
When you hover over it, you’ll see the following:
How To Fix This Issue
There are two ways to address this issue. One way is to manually remove the apostrophe.
This should convert the value into a number. As such, the leading zeros will no longer appear.
However, this could be tedious if you’re working on a large number of data. An efficient method to deal with this is to use one of the options given by the alert sign next to the cell.
For illustration, we’ll use the following data:
- Select the cells where you want to remove the leading zeros. In our illustration, it would be the cells in column A.
- Click the alert sign next to the selected cell/s. This will present us with several options. Select the Convert to Number option. This should convert the data into proper numbers.
- The numbers should appear without the leading zeros.
Remove Leading Zeros Appearing On Cells That Have A Custom Number Format
Another reason why leading zeros appear is that the cells have a customer number format. You can easily tell if a cell has a custom number format.
[1] When you look at the cell itself, you’d notice that the leading zeros appear.
[2] But when you take a look at the formula bar, the number has no leading zeros.
[3] And if you take a look at the cell format, it shows Special rather than General or Number.
What a custom format does is it changes the appearance of the value in the cell. It does not change the type of the cell (hence, it’s still a number). In this case, the custom format is set so that numbers on cells always show 6 digits.
How To Fix This Issue
Fortunately, it’s easy to fix this easy. We only need to set the cell format to General or Number.
- Select the cells where you want to remove the leading zeros. In our illustration, it would be the cells in column A.
- Open the Home tab. You should find a drop-down menu that shows the cell format. It should be showing Special. Click it. This will present you with several cell format options.
- Select General or any appropriate number format. For illustration purposes, select General.
- Doing so will remove any leading zeros in the selected cells.
Remove Leading Zeros Appearing On Cells Due To A Formula
Leading zeros appearing on cells may be due to a formula.
For example, using the TEXT function will convert any number to text so that it will always show at a certain length.
If the formula is =TEXT(12,“000000”), it will appear as 000012 on the cell.
What the TEXT function does here is that it sets any number to always appear with 6 digits.
Since 12 only has digits, four leading zeros were added to show 6 digits.
How To Fix This Issue
One way is to remove the formula so that only the number remains.
But that can be tedious. So we will go with the efficient way instead.
And that’s through using the VALUE function. What it does is convert the selected data into a number format.
- In a blank cell (preferably next to the cell that you’re working on), input the formula =VALUE (Cell#) where Cell# refers to the cell that you’re working on.
- Press Enter. The cell should show a number without leading zeros.
- To apply this to the rest of the column, you only need to double-click the fill handle.
Use Paste Special To Remove Leading Zeros
Another way to fix the issue of leading zeros appearing in cells is to use the paste special command.
We’ll be using the following data for illustration purposes:
- Select the cells where you want to remove the leading zeros.
- Copy the selected cells. You can do this by pressing the keyboard shortcut Ctrl + C or by pressing the Copy button found on the Home tab. A green dotted border should appear around the selected cells.
- On a blank cell (preferably the one next to the cell you’re working on), press right-click on your mouse. This will present you with several options. Click on the Paste Special option. This will open the Paste Special dialog box. Alternatively, you may press the keyboard shortcut Ctrl + Alt + V (after selecting the destination cell/s).
- On the Paste Special dialog box, make sure to tick the Add option under the Operation category. Then click the OK button.
- This will add the numerical value of the data to the destination cell/s. It will also remove any leading zeros.
Use the Text to Columns Function to Remove Leading Zeros
The Text to Columns function is another tool we can use to remove leading zeros.
We will use the following data for illustration purposes:
- Select the cells where you want to remove the leading zeros. In our illustration, it would be the cells in column A.
- Open the Data tab. You should find a Text to Columns button somewhere in the middle. Click it. This will open the Convert Text to Columns wizard. We will be doing 3 steps in this wizard.
- For step 1 of 3, choose the Delimited option under the Original data type category. Then click the Next button.
- For step 2 of 3, untick all the boxes under the Delimiters category. Click the next button after doing so.
- Finally, for step 3 of 3, select General under the Column data format category. For the destination, select the cell where you want to put the result. For this illustration, the destination will be the cells we’re working on (which should be the default). After doing so, click the finish button.
- The result will be numbers that don’t have leading zeros.
Conclusion
And that’s it for this article.
I hope that it will help you when you want to remove leading zeros in the Excel workbook or sheet that you’re working on.