How to Add Leading Zero in Excel

Excel automatically gets rid of leading zeros in numeric values as they are generally regarded as insignificant.

Not all leading zeros, however, are worthless. If you’re dealing with values such as your social security number, roll number, account number or any type of serial number, these unworthy digits are equally important.

You can format your value as a text to prevent any unwanted removals. For this, apply one of these three methods: adding an apostrophe (‘) symbol in front of your value, changing the cell format to text from the Home tab, or using the TEXT function.

If you’re importing data from a different file to Excel, you can also use New Query to import your value as a Text file. Additionally, you can create a custom cell format to keep the leading zeros.

Convert Value to Text

Excel will not remove your leading zeros if they’re formatted as text. This is because leading zeros are only redundant when entered in numeric digits.

Use Apostrophe Before Value

AAdding an apostrophe (‘) before you enter your data is probably the most effortless way to approach Excel removing leading zeros. When you add an apostrophe before your number, Excel will treat the following digit as text. This will prevent the program from removing leading zeros.

However, if you’re looking to convert a larger dataset, this method could be quite consuming.

Format Cell as Text

This will be the perfect method for you if you’re copy-pasting information with leading zeros to your Excel spreadsheet.

1. Open your worksheet.
2. Select the empty range you wish to paste your values with leading zeros.
   
3. Drop the menu down for General in the Numbers section of the Home tab.
   
4. Choose Text.
   
5. Paste or start entering your values.

Use TEXT Function

You can use the TEXT function to set a format keeping your leading zero. You can use the TEXT function in the =TEXT(cell, format) format. 

In this example, column A holds digits with no leading zeros. Let’s assume that the minimum length of your value is 5 and you want to add leading zeros in numbers less than this length. Your formula to add five leading zeros will look something like

=TEXT(A1, 00000)

Using this formula, A1 with a value of 7654 will be converted to 07654. You can change your format to add or decrease the leading zero count.

Apply Custom Cell Format

Excel allows you to create a custom cell format to keep as many leading zeros as you require. Similar to the TEXT function, you can set a format for your cell.

1. Open your workbook and select the range you wish to place your values.
2. On your keyboard, hit Ctrl + 1.
3. Choose Custom under Category on your left.
   
4. Under Type, enter your format.
   
5. Click OK.

For instance, if you set the format to 000000, 78 will be converted to 000078.

Import Data As Text File

If you’re extracting data from a text file, you can import it as a New Query. Your value will be treated as text, preventing Excel from removing the leading zeros.

However, if you make any changes to the text, Excel will automatically detect the cell contents as a number and remove the leading zeros. So we recommend this method only if you plan to keep the data static.

1. Head to the Data tab.
2. Select New Query from the Get & Transform section.
   
3. Choose From File > From Text.
   
4. Select your file from file explorer and select Open.
   
5. In the new window, select the fly-out menu next to Load > Load to.
   
6. Choose where you wish to load your content and select Load.