How to actually prevent Excel automatically removing leading zeros | Business Tech Planet (2024)

Key Takeaways

If you want to add leading zeros to a single cell, adding an apostrophe at the beginning is the simplest solution. [More…]

If you want to add leading zeros to multiple cells, rows, or columns, the best solution is to select the cells, right-click them, and select “Format cells.” Then, go to “Custom,” enter a zero for each digit under “Type” (if the maximum length of your number is five digits, enter five zeros), and click “OK.” [More…]

When you import documents into Excel for analysis, the numbers may appear differently than expected. That’s because Excel formats numbers to condense them, which is a problem for product codes or credit card numbers that often contain leading zeros. So, in this guide, we will look at how you can prevent Excel from removing leading zeros.

How to prevent Excel automatically removing leading zeros:

1. In Excel, select the cells, rows, or columns that need leading zeros.
2. Right-click the selected cells and choose “Format cells.”
3. Go to “Custom” on the left menu.
4. Under “Type,” enter the same number of zeros as there are digits in your number.
5. Then click “OK.”

• Get Excel for just $6.99 per month from the Microsoft Store!

If the number you want to store in Excel is five digits long, type “00000” without quotation marks in the “Type” field. Then, your custom format will replace thedefaultformat in the cells you selected. And when you enter “00123” into your formatted cells, the whole number will appear instead of “123.”

Please continue reading if the solution above does not solve your problem or if you need a detailed walkthrough of the steps provided.

We’ve also created a video to help guide you through the process:

See Also

Leading zeros in Excel: how to add, remove and hideExcel Tutorial: How Do I Keep The Zeros From Disappearing In ExcelKeeping leading zeros and large numbersExcel Tutorial: How To Make 0 Not Disappear In Excel

Overview: What are leading zeros, and why does Excel remove them automatically?

Leading zeros are any zeros that come before a number greater than zero in a series of digits. For example, James Bond’s famous 007 identification has two leading zeros. However, if you enter “007” into a cell in your Excel worksheet, the default number format will remove the leading zeros.

Excel removes leading zeros because they do not affect the value of the number and are therefore seen as unnecessary. But some account or bank numbers require leading zeros, so you must create a custom format to retain them.

How to prevent Excel automatically removing leading zeros

In this guide, we will look at several solutions to prevent Excel from removing leading zeros. You can jump to the most relevant solution by clicking the links below or trying all solutions to find the best fix.

• Solution 1: Use a custom number format.
• Solution 2: Use a text format.
• Solution 3: Use a formula.
• Solution 4: Use an apostrophe.
• Solution 5: Use third-party tools.

Solution 1: Use a custom number format to prevent Excel automatically removing leading zeros

This solution is an expansion of the steps provided at the top of this guide. The solution will work on the desktop and web versions of Microsoft Excel.

In this demonstration, we have created a simple spreadsheet with an “Account Number” column that contains a ten-digit number with two leading zeros removed.

• Open your Excel worksheet.
• Select the cells, rows, or columns that need leading zeros. (1)
  • In this demonstration, we clicked column header, “D.”
• Right-click the selected cells. (2)
• Then choose “Format cells” from the popup menu. (3)

• The Format Cells dialog box will open.
• Ensure you are in the “Number” tab at the top of the dialog box. (1)
• Go to “Custom” on the left menu. (2)
• Under “Type,” enter the number of zeros in your number. (3)
  • The “Account Number” in this demonstration contains ten digits.
  • To retain leading zeros, we entered ten zeros in the “Type” field.
• Then click the “OK” button. (4)

The result shows that all the account numbers now have leading zeros, and each number is ten digits in length. Also, because we formatted the whole column, all additional account numbers will retain their leading zeros.

Solution 2: Use a text format to prevent Excel automatically removing leading zeros

This solution will not add leading zeros to existing numbers. Instead, it will format the cells so that any numbers added will be treated as text and retain their leading zeros.

• Open your Excel worksheet.
• Select the cells, rows, or columns that need leading zeros. (1)
  • In this demonstration, we clicked column “D.”
• Right-click the selected cells. (2)
• Then choose “Format cells” from the popup menu. (3)

• The “Format Cells” dialog box will open.
• Ensure you are in the “Number” tab at the top of the dialog box. (1)
• Go to “Text” on the left menu. (2)
• Then click the “OK” button. (3)
• Alternatively, click the select box in the “Number” section in the ribbon.
  • The default format is “General.”
• Then select “Text” at the bottom of the dropdown list.

The result shows that the existing numbers have not been affected. However, all additional numbers added to the “Account Numbers” column will retain their leading zeros.

Solution 3: Use a formula to prevent Excel automatically removing leading zeros

This solution will use a formula in two ways. First, we will show you how to format the number similar toSolution 1using the Excel TEXT function. And second, we will show you how to add zeros to the beginning of your number.

Method 1: Use a TEXT formula to format the number

We have created an additional column adjacent to the “Account Number” column. This new column will contain a formula that will correctly format the account number.

• Open your Excel worksheet.
• Double-click an empty cell, preferably next to your existing number. (1)
• Paste the following formula into the cell:

=TEXT(CELL,"0000000000")

• Change “CELL” to the cell address of your first number.
  • The cell address of our first account number is “D2.”
• Change the number of zeros in the quotation marks to match the maximum length of your number.
  • Our account numbers are ten digits long, so there are ten zeros in the quotation marks.
• Then press the ↵ Return key.

Method 2: Use a formula to add zeros

• Open your Excel worksheet.
• Double-click an empty cell, preferably next to your existing number. (1)
• Paste the following formula into the cell:

="00"&CELL

• Change the number of zeros in the quotation marks as required.
  • Whatever is written in the quotation marks will appear at the beginning of the cell.
• Change “CELL” to the cell address of your first number.
  • The cell address of our first account number is “D2.”
• Then press the ↵ Return key.

Solution 4: Use an apostrophe to prevent Excel automatically removing leading zeros

If you insert an apostrophe before a number, Microsoft Excel will ignore the cell formatting and show the number as input. This approach ensures that even if the cell formatting is changed, the cell content will retain its original appearance.

If you want to add an apostrophe to all existing numbers, you can do that by building a macro in Visual Basic (VBA). Assuming that your numbers are missing their leading zeros, you will need to add an apostrophe and the leading zeros.

• Open your Excel worksheet and go to the “View” tab in the top menu. (1)
• Click on the down-arrow under “Macros.” (2)
• Select “Record Macro” from the dropdown menu. (3)
• The “Record Macro” dialog box will pop up.
• Enter a “Macro name.” (4)
• Add an optional shortcut key. (5)
• Then press the “OK” button. (6)

• Now go back to “Macros” in the menu and select “Stop recording.”
• Return to “Macros” again and select “View Macros.”
• Then click the “Step Into” button in the popup box.

• The VBA editor will now open, allowing you to build the macro.
• Paste the following code just above “End Sub” in the editor:

Dim cell As RangeFor Each cell In SelectionIf cell.Value <> "" Then cell.Value = "'00" & cell.ValueNext

• The complete code should look similar to the following:

Sub Apostrophe()'' Apostrophe Macro'' Keyboard Shortcut: Ctrl+a'Dim cell As RangeFor Each cell In SelectionIf cell.Value <> "" Then cell.Value = "'00" & cell.ValueNextEnd Sub

• Now return to your spreadsheet and select the cells to add leading zeros.
• Then press the Ctrl + a keys (or your chosen shortcut) to run the macro.

The apostrophes will be ignored, allowing you to utilize the MATCH and VLOOKUP tools.

Solution 5: Use third-party tools (Ablebits or Kutools) to prevent Excel automatically removing leading zeros

Ablebits Ultimate Suite

Ablebits will expand your Excel capabilities with over 70 professional tools and 300+ options. You can download and use Ablebits with a 14-day free trial, which grants you access to all the features required to add or remove leading zeros.

70 professional tools and over 300 options
Select cells by value or color
Convert numbers to words
Split names into multiple columns

$99 One-time purchase – 14-day free trial

• Close your Excel application and download Ablebits.
• Once installed, reopen your worksheet.

• You should notice two new tabs at the top of Excel:
  • Ablebits Data
  • Ablebits Tools
• First, select the cells, columns, or rows you want to add leading zeros to. (1)
• Then go to “Ablebits Tools.” (2)
• Click “Utilities” on the right side of the top menu. (3)
• Then click “Add/Remove Leading Zeros.” (4)

• A dialog box will open.
• Select your range. (1)
  • If you’ve selected the cells to add zeros to, they will appear in the range box.
  • Otherwise, select your range in the worksheet without closing the dialog box.
• Check the back up checkbox, as changes using this method cannot be undone with Ctrl + Z.
• In our example, we want account numbers with ten digits. (2)
• Click the “Apply” button to make the changes. (3)

The result transforms the numbers into text and prepends zeros to the beginning of the cell. If you want to undo this action, you can close and reopen your workbook without saving.

Alternatively, you can return to “Utilities” > “Add/Remove Leading Zeros” and change the digits to a length that is equal to the current length minus the number of leading zeros.

In our example, we have ten digits with two leading zeros. To return the column to its previous state, we can set the number of characters to 8 instead of 10. Then, when we click “Apply,” the leading zeros will be removed. However, the column will remain formatted as text.

To change the data back to numeric values, select the cells and click the warning icon. Then, select “Convert to Number” from the dropdown list.

Kutools for Excel

Like Ablebits, Kutools has over 300 advanced tools and functions, including the option to add or remove leading zeros. You can also create searchable dropdown lists that will condense your data footprint.

More than 300 additional tools and options
Encrypt your workbooks
Create mailing lists
Use background and font color to sum cells.

$49 One-time purchase – 30-day free trial

• First, close your Excel application and download Kutools.
• Once installed, reopen your worksheet.
• You will see two new tabs at the top of Excel:
  • Kutools ™
  • Kutools Plus
• Select the cells, rows, or columns to add leading zeros to. (1)
• Go to the “Home” tab at the top. (2)
• Click the select box in the “Number” section. (3)
• Set the data type to “Text” from the dropdown menu. (4)

• If necessary, reselect the cells, rows, or columns to add leading zeros to. (1)
• Go to “Kutools ™” at the top. (2)
• Click “Text” in the “Editing” section. (3)
• Then select “Add Text” from the dropdown menu. (4)

• Enter the number of zeros you want to add in the “Text” box.
• Select “Before first character” as the “Position.”
• Then click the “OK” button.

Frequently Asked Questions (FAQ)

How do you format a cell to keep leading zeros?

The simplest way to format a single cell so that it keeps leading zeros is to add an apostrophe at the beginning of the cell.

How do you keep leading and trailing zeros in Excel?

We’ve discussed how to keep leading zeros in this guide. However, if you want to keep trailing zeros (those which appear after decimals), you can do that by following these simple steps:

• Select the cells, rows, or columns to add trailing zeros to. (1)
• Right-click the selected cells. (2)
• Then choose “Format cells” from the popup menu. (3)

• The “Format Cells” dialog box will open.
• Ensure you are in the “Number” tab at the top of the dialog box. (1)
• Go to “Custom” on the left menu. (2)
• Under “Type,” enter “0.00” to add two trailing zeros. (3)
• Then click the “OK” button. (4)

Conclusion

If you want to keep leading zeros in a single cell, the simplest solution is to add an apostrophe to the beginning of the cell. If you want to add leading zeros to multiple cells, rows, or columns, you can format the cells using Solutions 2 or 3.

As there are native solutions for adding leading zeros, we do not advise purchasing third-party software for that sole purpose. However, given the array of tools on offer in Ablebits and Kutools, it’s certainly worth looking at if you’re a regular Excel user.

Thanks for reading.