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:
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:
=(cell) * 1=(cell) + 0
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:
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:
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:
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:
