How to stop Excel automatically removing leading zeros

So you have just imported your very important accounts document from a website over to your desktop for analysis. You open the doc only to find the numbers look off. Something that really frustrates a lot of users is the fact that the numbers they add are just being configured to make them smaller.

Understandably, Excel does this to reduce the size of data records — helpful if you have masses of data — however, some users may not find the appeal in having their numbers being altered all the time. If you’ve ever tried to enter numbers into an Excel spreadsheet, you’ve probably seen that Excel reformats your numbers by eliminating leading zeros, converting a fraction to date, or converting a long string of numbers or decimals to scientific notation. This might be aggravating and hinder your records from properly importing. Follow through for more information on how you can resolve this.

How to prevent Excel automatically removing leading zeros:

  1. Open the web version or desktop version of Microsoft Excel.
  2. Select the column(s) that have leading zero removed (use shift to select multiple columns).
  3. Now right click then click on “Format cells”.
  4. After that, navigate to “Numbers”.
  5. Click on “Custom”.
  6. Under “Type”, type in “0”, (equivalent to the number of digits you have).
  7. Click on “OK”.
  • Get Excel for just $6.99 per month from the Microsoft Store!

Once this is complete, you will be able to automatically prevent Excel from removing the leading zeros. This process essentially removes the cell formatting and adds a custom digit field, where numbers displayed are in full form with the correct leading zeros. You can revert back by reversing the process and selecting general. This will restore the columns or cells back to the original condensed format. Only selected columns will be affected, this is because the cell formatting is only applied to the cells of interest.

This approach is best utilized when working with a numeric dataset and the outcomes should be numbers rather than words. It simply affects how a number is shown, not the number itself: leading zeros appear in cells, but the actual value appears in the formula bar. When such cells are referenced in formulae, the computations are performed with the original values. Custom formats may only be applied to numeric data (numbers and dates), with the outcome also being a number or date.

security office365 windows 11
info Recommended Software

Use “Get (Power Query)” to prevent Excel automatically removing leading zeros (Windows)

You can use another set of different properties to allow for your sheets to be replicated in formatting. This includes preventing the lead zeros from being removed. When importing data, use Excel’s Get & Transform (Power Query) experience to format specific columns as text. Although we are importing a text file in this example, the data transformation processes are the same for data imported from other sources such as XML, Web, JSON, and so on.

  • Firstly, open your document.
  • After that click on the “Data” tab.
  • Now choose Text/CSV from the drop-down menu next to the Get Data button.
  • Excel will populate a preview window with your data. To open the Query Editor, click Edit in the preview window.
  • If any columns need to be changed to text, click on the column header to pick it, then proceed to Home > Transform > Data Type > Text.
  • Then, in the Change Column Type window, click Replace Current, and Excel will change the selected columns to text.
  • When you’re finished, click Close & Load to have Excel return the query data to your worksheet.

If you wish to remedy the issue just inside the workbook since it isn’t utilized as a data source by other applications, you can use a custom or special format to maintain the leading zeros. This is valid for number codes with fewer than 16 digits. You may also format your number codes with dashes or other punctuation characters. To make a phone number more legible, use a dash between the international code, the country/region code, the area code, the prefix, and the final few numbers, for example.


excel basics advanced excel excel workbook

Use the Apostrophe Technique to prevent Excel automatically removing leading zeros

By inserting an apostrophe before the number, you are instructing Microsoft Excel to ignore the cell formatting and show the number as input. This approach ensures that even if the cell formatting is changed back to General and a cell is edited, it will retain its original appearance rather than being auto-formatted by Excel.

  • Put an apostrophe in front of the cell number.
  • (For example, ‘001234’ in Excel will display 001234)
  • Enter the number exactly how you want it to appear and hit Enter.
  • The numbers should now appear and import properly.
  • This enables you to utilize the MATCH and VLOOKUP tools in Excel (Apostrophe will be ignored)

If you’ve ever tried to enter numbers into an Excel spreadsheet, you’ve probably seen that Excel re-formats your numbers to something else, such as eliminating leading zeros, converting a fraction to date, or converting a long string of numbers or decimals to scientific notation. This might be aggravating and prevent your records from importing successfully. Simply follow the steps outlined above to prevent Microsoft Excel from altering the layout of your data.

When dealing with spreadsheets, you may require a column with leading zeros. Let’s take an ID number as an example: if I enter “0010” as the beginning of my id, Excel will automatically erase the first two zeros.

surface pro 8Surface Go 3surface pro 8

How to prevent Excel automatically removing leading zeros

A company wants to import this sheet of products onto their site however the site has failed to import the sheet because the sheet import handle can only import products with a minimum three-digit handle. Anything less won’t process correctly, hence they have been barred from import. How can we allow the company to import the handle following the minimum three-digit protocol? We can import using leading zeros as a form of digits. Follow through to see how you can achieve this.

Step by step breakdown

  • Select the column(s) that have leading zero removed (use shift to select multiple columns).
  • In the desktop version, select “Format cells”.
  • In the web version, select “Number format”.
  • In the desktop version, navigate to “Number”.
  • Click on “Custom”.
  • Under “Type”, type in “0”, (equivalent to the number of digits you have).
  • Click on “OK”.

Results

We now have how leading zeros and now it’s ready to be imported onto our website as we have fulfilled the minimum digit criteria. You can adjust any columns that have special requirements to be imported, this may also include the price it’s under a pound you can add a “0.” configuration to ensure that it remains consistent with the remaining price figures.


That’s it for this Blog thank you for taking time out to read our content, please feel free to email our team about how it went if you followed the steps or if you need more help with the questions we answered in this Blog.

Saajid Gangat

Saajid Gangat has been a researcher and content writer at Business Tech Planet since 2021. Saajid is a tech-savvy writer with expertise in web and graphic design and has extensive knowledge of Microsoft 365, Adobe, Shopify, WordPress, Wix, Squarespace, and more! You can connect with Saajid on Linkedin.

Recent Posts