To keep a leading zero in Excel, format the cell as Text before typing or importing an identifier, or use a custom number format such as 00000 when you only need the zero to appear on screen. If the number is already in A2 and you need a five-character text result, use =TEXT(A2,"00000"). The right choice depends on whether the zero is part of the data itself or only its display.
Excel normally treats entries such as postal codes, product IDs, and account numbers as numbers. Since a zero at the beginning does not change a number’s value, Excel removes it. The methods below explain how to preserve the characters, show a fixed-width number, and handle data that has already lost its zeros.
What You’ll Need
- Excel for Microsoft 365 on Windows, or access to the workbook in Excel for Mac or Excel for the web.
- The data you want to enter or correct, and, for a custom format or formula, the number of digits you want to display.
- If Excel has already removed zeros, the intended length or original source data. Excel cannot determine how many zeros were there just from the shortened number.
Step-by-Step
- Choose whether you need stored zeros or just visible zeros. Use Text when the value is an identifier and must remain exactly as entered, such as 00427. Use a custom number format when the underlying value should still be the number 427 but needs to display as 00427 in this workbook. A formula is useful when you want to create a separate, padded text result. These distinctions matter for codes and IDs, which usually should not be added, averaged, or used in arithmetic.
- Set cells to Text before entering identifiers. Select the destination cells or column. In Excel for Microsoft 365 on Windows, go to Home > Number Format and choose Text from the dropdown. Type the value, including its leading zeros, and press Enter. For example, typing 00427 into a cell formatted as Text keeps all five characters. On Excel for Mac, use the same Home tab and Number Format dropdown. In Excel for the web, select the cells, open the number format dropdown on Home, and choose Text. Set the format first; changing a cell to Text after entry does not bring zeros back.
- Enter one value as text with an apostrophe. If you only have a few entries, type an apostrophe before the value, for example
'00427, then press Enter. Excel uses the apostrophe to interpret the entry as text and does not show the apostrophe in the cell. The displayed value is 00427. This is quick for occasional entries, but formatting a whole column as Text is more reliable for a long list or pasted data. The apostrophe is an entry instruction, not an extra character in the displayed value. - Use a custom number format to display a fixed number of digits. Select the cells containing numeric values. On Windows, press Ctrl+1 to open Format Cells; on Mac, press Command+1. You can also open the Format Cells dialog from the Number group on the Home tab. Select Number > Custom, click the Type box, enter
00000for a five-digit display, and select OK. A value of 427 will display as 00427, while the stored value remains 427. Replace the number of zero placeholders with the total width you need: for an eight-digit code, use00000000. - Check what the custom format does to values of different lengths. A zero placeholder sets a minimum display width; it does not cut off longer values. With the format
00000, 427 displays as 00427 and 12345 displays as 12345. A value such as 123456 displays as 123456, not as a shortened five-digit number. If the code length varies, or the zeros are meaningful characters regardless of length, store the entries as Text instead of relying on a fixed-width numeric display. - Use TEXT to make a padded text result from existing values. Suppose numeric values are in A2:A20 and you want five digits in column B. In B2, enter
=TEXT(A2,"00000")and press Enter; if A2 contains 427, the result is the text 00427. Fill the formula down the column using the fill handle or by copying and pasting it into the remaining rows. The result is text, not a number, so it is suitable for displaying or combining identifiers but not for arithmetic. The format code in the formula controls the minimum number of displayed characters. - Replace formulas with fixed text if you need a standalone list. After checking the padded results, select and copy the formula cells. Use Home > Paste > Paste Values to replace formulas with their displayed text results. This is useful before sharing the list or removing the original source column. Keep a copy of the original data until you have confirmed the converted values are correct. Paste Values is available in desktop Excel; in Excel for the web, use the Paste dropdown and choose the values-only option if shown.
- Import code columns as text when bringing in a file. For a CSV or text file in Excel for Microsoft 365 on Windows, choose Data > From Text/CSV. In the preview, choose Transform Data if you need to set a column’s type before loading it. In Power Query, select the identifier column, open its data type menu, and set it to Text, then choose Close & Load. This prevents automatic number conversion from discarding leading zeros during import. If you open a CSV by double-clicking it, Excel may interpret digit-only columns as numbers, so importing through the Data tab gives you more control. The available import options can differ in Excel for Mac and the web; where Power Query import controls are unavailable, import in desktop Excel or prepare the source column as text.
- Verify the result before using or exporting it. Click a cell and look at the formula bar, or check whether Excel treats the entry as text or a number. For a text identifier, the leading zero should be part of the cell’s content. For a custom format, the formula bar will show the underlying number without added zeros, even though the grid displays them. Check several rows, including the shortest and longest codes. If you export to CSV or use the data in another program, reopen or preview the exported file to confirm the zeros are present; number formatting may not travel with the data consistently.
Common Mistakes
Changing the format after Excel has removed zeros. Selecting Text or applying 00000 after entry can change how the current value is handled or displayed, but it cannot recover unknown original digits. If the cell contains 427 and the original code could have been 427, 0427, or 00427, find the intended width from a reliable source before rebuilding it.
Using a custom format when the characters must travel with the value. A custom format changes the display, not the stored number. Another workbook, database, or import process may use the underlying 427 rather than the visual 00427. Use Text or a TEXT formula result when the zeros need to be part of the actual data.
Assuming a five-zero format means exactly five digits. The format 00000 pads short numbers to five positions but does not limit longer numbers. If a code must be exactly five characters, validate the input length separately rather than assuming the format will reject extra digits.
Typing an apostrophe after the zeros or including it in a formula result. The apostrophe goes before the first digit when you type directly into a cell. It should not be included in a custom number format or added to a TEXT formula. For bulk entry, preformatting the destination range as Text is easier to repeat consistently.
Expecting a formula to repair data without knowing its intended width. TEXT needs a format pattern. Adding zeros to make every value eight characters is only correct if eight digits is the actual rule for that column. Confirm the rule before filling a formula down, especially for mixed identifiers.
Tips
For postal codes, employee numbers, SKUs, and other identifiers, ask whether calculations on the value are meaningful. If not, Text is usually the clearest storage choice. For a fixed-width numeric code that is used in calculations, a custom format keeps the underlying value numeric while making the worksheet easier to read.
Apply the format to the entire destination column before pasting a list, but avoid formatting thousands of unused columns if the workbook is already large. For a clean import, set the type of the relevant Power Query column to Text rather than changing the type after Excel has loaded it as a number. Keep a separate source copy when converting or cleaning important data.
Use a consistent width throughout a column. If some IDs are five digits and others are six for a valid reason, do not pad everything to five and assume the result is uniform. Add a separate check or rule for the actual code system. Also remember that leading zeros are different from decimal places: a number format such as 0.00 controls digits after the decimal point, not zeros at the start.
When formulas refer to the padded values, remember that TEXT returns text. Text values may sort differently from numeric values: for example, text codes sort character by character, while numbers sort by numeric size. For fixed-width identifiers, text sorting is often desirable because each code has the same number of characters. If you need mathematical calculations, keep the original numeric column and use the padded result only for display or export.
Troubleshooting
The zero still disappears when I type. Check that the destination cell is formatted as Text before you enter the value. If you entered it already, edit the cell and retype the digits after changing the format, or use an apostrophe before the digits. Formatting an already-entered number as Text alone does not recreate characters Excel discarded.
The custom format does not appear to work. Select the cell and confirm that the entry is a number, then open Format Cells and check the Custom Type box for the correct count of zero placeholders. If the cell contains text, a numeric custom format may not pad it. Use Text entry or the formula method for text values.
The formula shows the formula text instead of a result. Make sure the formula begins with an equals sign and that the destination cell is not formatted as Text. Change the cell format to General, then re-enter the formula. Also check that there is no apostrophe or leading space before the equals sign. With a number in A2 and the formula entered in B2, =TEXT(A2,"00000") should return a five-character text result.
Zeros vanish after copying or exporting. If the source uses a custom number format, the receiving program may not preserve that display rule. Create a text result with TEXT, copy it, and paste values where appropriate. For CSV, inspect the exported file or import it into a blank workbook with the identifier column explicitly set to Text. Do not rely only on how the original worksheet looks.
The length is wrong or some codes have extra digits. Confirm the required width against the source system. A pattern with five zeros pads values shorter than five digits but leaves longer values intact. If the source has already removed zeros, use the known official width to rebuild the text, or retrieve the original codes. Do not guess when a wrong identifier could lead to a mismatched record.
Frequently Asked Questions
How do I keep one zero at the beginning of a number in Excel?
For a single entry, type an apostrophe followed by the value, such as '0427. For a whole range, format the cells as Text before entering or pasting the values.
How do I show leading zeros without changing a number?
Apply a custom number format with enough zero placeholders, such as 00000 for a five-digit display. Excel keeps the numeric value unchanged, so 427 displays as 00427.
Can I get zeros back after Excel removes them?
Only if you know the intended width or can recover the original data. If every code should have five digits and the shortened number is in A2, a TEXT formula can rebuild a five-character result. Without that rule, Excel cannot tell how many zeros were originally present.
Does the TEXT formula return a number?
No. TEXT returns text formatted according to the pattern. That makes it useful for IDs and exports, but keep a separate numeric value if you need to calculate with the original number.
Will leading zeros remain when I save as CSV?
Do not assume they will. A custom number format is a display setting and may not be retained when another program reads the CSV. Check the exported file, or create a text version of the codes and verify the result after export.
Related
- How to Import an Excel Spreadsheet Into a Word Document
- Import Excel into a SharePoint List: Step-by-Step
- How to Highlight a Row in Excel with a Keyboard Shortcut
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Mac Keyboard Shortcuts: Essential Keys and TipsIn Excel for Mac, use Command+C, Command+V, and Command+Z to copy, paste, and undo; use Command+Arrow to move…
- How to Track Spending in Google Sheets Step by StepTo track spending in Google Sheets, create a transaction list with a date, description, category, and amount, then…
- How to Compare Two Excel Sheets for Matching ValuesTo find values from one sheet anywhere in another, put this formula beside the first value: =IF(COUNTIF(Sheet2!$A:$A,A2)>0,"Match","Missing"). With…
- Schedule an Email in Outlook Desktop: Step-by-StepTo schedule an email in Outlook desktop, compose your message and choose a future send time: in new…
- Compare Two Excel Sheets for Matches and DifferencesYes. In Excel for Microsoft 365, you can compare two sheets by checking whether each key value appears…
- Currency Exchange in Google Sheets: Rates and FormulasTo get a currency exchange rate in Google Sheets, enter a formula such as =GOOGLEFINANCE("CURRENCY:USDEUR"), which requests the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.