4 Easy Ways to Keep Leading and Trailing Zeros in Excel

Use power query or custom formatting

Excel automatically removes leading and trailing zeros. If this disrupts your ability to store data where these are important, such as postal codes, you can import the data as text so it appears exactly as entered. If you need to use the data in calculations, set it to display in a custom format instead. This wikiHow article will show you 4 ways to prevent Excel from removing leading and trailing zeros.

Method 1
Method 1 of 4:

Storing Numbers as Text

  1. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 1
    1
    Add an apostrophe before the number. If you only need to correct a few cells, you can fix them one at a time. Edit each cell to start with an apostrophe ('), then type the number exactly as it should appear. The apostrophe tells Excel to store this cell as text, meaning it cannot be used for formulas.
    2. Advertisement
Method 2
Method 2 of 4:

Using Get & Transform

  1. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 2
    1
    Open a new, blank workbook. The Get & Transform tool (also called Power Query) allows you to customize the data you import from a data source. This tutorial will cover importing from a text file, but the steps will work for other source types as well![1]
    • Excel automatically and permanently removes leading zeros when you import data into a workbook. The Get & Transform utility allows you to designate certain columns as text during the data importing process. This keeps the leading zeros intact.
    • Alternatively, you can open an existing workbook if you have one that you want the data to be imported to.
    • Refer to our article on downloading Excel if you need to get Excel on a new device.
  2. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 3
    2
    Go to the Data tab.
  3. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 4
    3
    Click From Text/CSV. This option is located next to the Get Data button.
    • If you don’t see this option, you may have a version of excel that is arranged differently. Follow the method below (Importing Data as Text) instead.
  4. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 5
    4
    Browse to and select your text file. Then press Import. This will open a preview menu for the data you’re importing into your workbook.
  5. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 6
    5
    Click Edit. This will start the Query Editor.
  6. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 7
    6
    Click the column header for the variable you want to keep as text. Then follow these steps to convert the column to text:
    • Go to the Home tab.
    • Locate the Transform section.
    • Click Data Type.
    • Choose Text. This will open a confirmation “Change Column Type” window.
    • Click Replace Current in the Change Column Type window.
    • Repeat this process for any columns with leading zeros that you want to keep.
    • You can do this for several columns at the same time by selecting multiple column headers with Ctrl + Left-Click.
  7. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 8
    7
    Click Close & Load. Excel will import the data into your worksheet. Check your columns to make sure the leading zeros you want to keep are present.
    8. Advertisement
Method 3
Method 3 of 4:

Importing a Data Text File

  1. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 2
    1
    Save a large database in text format. Once you import data into a default Excel workbook, the leading and trailing zeros disappear permanently. The easiest way to fix this is to clear the faulty data and start again. Return to your original database and save it as a .csv file or a .txt file.
  2. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 3
    2
    Create a new Excel workbook. You will import your data into this workbook as text, meaning it will not be usable in formulas. You will, however, keep the formatting.
    • This method does allow you to import some columns as text and some columns as numbers. Only the text columns will keep leading and trailing zeros.
  3. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 4
    3
    Open the Text Import Wizard. Click the Data tab on your ribbon menu, then look in the External Data Sources section. Click the icon labeled Text or From Text.[2]
  4. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 5
    4
    Import data from the text document. Select the text file in the "Choose a File" window. Follow the onscreen instructions to complete Steps 1 and 2.
  5. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 6
    5
    Set columns to text format. In Step 3 of the Text Import Wizard, you should see a preview of your spreadsheet. Click on a column to select it, then select an option to set the format for that column. Specifically, to keep leading and trailing zeros in columns containing phone numbers and similar data, select those columns and choose Text. You can now complete the import and load your spreadsheet with the zeros, as intended.
    6. Advertisement
Method 4
Method 4 of 4:

Creating Custom Formats

  1. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 7
    1
    Select the cells you need to change. A custom number format specifies the exact format for your data, including the number of leading or trailing zeroes. To begin, select the columns, rows, or groups of cells you'd like to change.
  2. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 8
    2
    Open the Format window. Click the Home tab on the ribbon and look in the Number group. On Excel for Windows, click the dialog launcher next to the word Number (a small arrow in a square).[3] [4] On Excel for Mac, click the dropdown menu (it says General by default) and select Custom....
    • On old versions of Excel without a ribbon menu, instead click Format → Cells → Number tab.[5]
  3. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 9
    3
    Create a custom format. In Excel 2013 and Excel 2016, just select any format on the list. When you edit that default format, Excel will automatically create a new custom format without altering the default option.[6] On all other versions of Excel, select Custom from the list of options instead. Look for a text box labeled Type where you can enter your custom format.
    • If your data represents calendar dates, phone numbers, or other common data types, browse the list of default options. You may be able to use one of them rather than creating your own format.
  4. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 10
    4
    Add leading zeroes. Custom formats use several "placeholder characters" to specify how digits should display. The character 0 (zero) tells Excel to display a zero in that position if there is no other digit there.[7] For example, the format 000 will add leading zeroes to the hundreds and tens place if necessary. 97 will display as 097, and 3 will display as 003.
    • Numbers to the left of the decimal point are always displayed. Even though there are only three digits in the format code 000, the number 3750 will still display as 3750.
  5. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 11
    5
    Specify decimal places and trailing zeroes. Custom number formats will delete all digits after the decimal point unless you specify otherwise. To change this, just add a decimal point to the end of your custom code, followed by 0 (or one of the placeholder characters described below).[8] For example, 000.000 will round to the nearest thousandth, and add up to three trailing zeros after the decimal point. Here's how various numbers look in the 000.000 format:
    • 13.1 becomes 013.100
    • 95001 becomes 95001.000
    • 5.0057 becomes 5.006
  6. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 12
    6
    Use the pound sign to prevent extra zeroes. The symbol # is another placeholder character in custom formats. This will prevent all leading zeroes if used at the front of the number, and prevent all trailing zeroes if used after the decimal point.[9] For example, the custom format 00.## allows for a leading zero in the tens place, but no trailing zeroes:
    • 5.8 becomes 05.8
    • 2.71 becomes 02.71
    • 15.0 becomes 15
    • 38.315 becomes 38.32
  7. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 13
    7
    Add leading zeros as text. Excel treats anything inside quotation marks as text in your custom format, and will display it exactly as you typed it.[10] This may be useful if each cell in your original database had exactly two leading zeros, for example. To restore these, set your spreadsheet to the custom format "00"#.
    • If your data includes digits after a decimal point, prevent rounding by using the custom format "00"#.## instead, using as many # or 0 characters after the decimal point as necessary.
  8. Image titled Prevent Excel from Removing Leading & Trailing Zeros Step 14
    8
    Align decimal points with a question mark. If your goal is to align the decimal points in a column, there's no need to add leading or trailing zeroes. Use the ? character in your custom format, and Excel will add empty spaces instead to align the digits in the column. For example:
    • ?.??? rounds to the nearest thousandth with no trailing zeroes, and aligns the decimal points.
    • 000.? adds leading zeros up to the thousands place, rounds to the nearest tenth with no trailing zeros, and aligns the decimal points.
    • ?.00 rounds to the nearest hundredth with trailing zeros and aligns the decimal points.
    9. Advertisement

Warnings

  • If you store a number as text you will not be able to perform calculation on that cell.
    ⧼thumbs_response⧽
Advertisement

You Might Also Like

Create Pivot Tables in ExcelHow to Create Pivot Tables in Microsoft Excel to Analyze Data
Make a Spreadsheet in Excel
How to
Make a Spreadsheet in Excel
Create a Mortgage Calculator With Microsoft Excel
How to
Create a Mortgage Calculator With Microsoft Excel
Unprotect an Excel Sheet
How to
Unprotect an Excel Sheet
Create a Timeline in ExcelHow to Create a Timeline in Excel: SmartArt, Templates, and More
Create a Graph in Excel
How to
Create a Graph in Excel
Change from Lowercase to Uppercase in Excel
How to
Change from Lowercase to Uppercase in Excel
Merge Cells in ExcelHow to Merge Cells in Microsoft Excel: A Quick Guide
Insert Pictures in Excel That Automatically Size to Fit Cells
How to
Insert Pictures in Excel That Automatically Size to Fit Cells
How to
Unhide Rows in Excel
Use ExcelNew to Excel? Here's Super Easy Tricks to Get You Started
Convert Excel to Word3 Easy Ways to Convert Microsoft Excel Data to Word
Find Duplicates in Excel
How to
Find Duplicates in Excel
Link Sheets in Excel
How to
Link Sheets in Excel
Advertisement

References

  1. ↑ https://support.microsoft.com/en-us/office/keeping-leading-zeros-and-large-numbers-1bf7b935-36e1-4985-842f-5dfa51f85fe7
  2. ↑ https://support.office.com/en-US/article/text-import-wizard-c5b02af6-fda1-4440-899f-f78bafe41857
  3. ↑ https://support.office.com/en-us/article/Keep-leading-zeros-in-number-codes-1bf7b935-36e1-4985-842f-5dfa51f85fe7
  4. ↑ https://support.office.com/en-us/article/Create-or-delete-a-custom-number-format-83657CA7-9DBE-4EE5-9C89-D8BF836E028E
  5. ↑ https://support.microsoft.com/en-us/kb/81518
  6. ↑ https://support.office.com/en-us/article/Create-or-delete-a-custom-number-format-83657CA7-9DBE-4EE5-9C89-D8BF836E028E
  7. ↑ https://support.office.com/en-us/article/Create-or-delete-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4
  8. ↑ https://support.office.com/en-us/article/Create-or-delete-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4
  9. ↑ https://support.office.com/en-us/article/Create-or-delete-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4
  1. ↑ https://support.microsoft.com/en-us/kb/81518
  2. ↑ https://support.office.com/en-us/article/Keep-leading-zeros-in-number-codes-1bf7b935-36e1-4985-842f-5dfa51f85fe7

About This Article

Kyle Smith
Written by:
Kyle Smith
wikiHow Technology Writer
This article was co-authored by wikiHow staff writer, Kyle Smith. Kyle Smith is a wikiHow Technology Writer, learning and sharing information about the latest technology. He has presented his research at multiple engineering conferences and is the writer and editor of hundreds of online electronics repair guides. Kyle received a BS in Industrial Engineering from Cal Poly, San Luis Obispo. This article has been viewed 173,684 times.
How helpful is this?
Co-authors: 10
Updated: January 20, 2023
Views: 173,684
Categories: Microsoft Excel
Advertisement