To add a zero before a number in Excel, enter it as text (type an apostrophe first, such as '0123), use a formula such as ="0"&A2 for an existing value, or apply a number format if you only need the zero to appear on screen. Choose text for codes and identifiers; use number formatting when the value should remain a number.
What You’ll Need
- Excel for Microsoft 365 on Windows, and the cells containing the values you want to change.
- If you use a formula, an empty column for its results. Keep the original column until you have checked the results.
- A decision about whether the zero must become part of the stored value or only appear in the cell. This matters because Excel normally removes leading zeros from numbers.
Step-by-Step
- Decide what the zero is for. If the value is an identifier, such as a product code, postal code, account reference, or ticket number, it is usually better to store it as text. Identifiers are labels, not amounts to calculate. If you need the cell to remain numeric and only want a consistent display, use a custom number format instead. If you need to create a new text value from existing numbers, use a formula.
- Enter a new value with its leading zero preserved. Select a blank cell, type an apostrophe, then type the value, for example
'0123, and press Enter. The apostrophe tells Excel to treat the entry as text; it will not appear in the cell after you confirm the entry. You can also select the cells first and choose Home > Number Format > Text, then type the value, such as0123. Format the cells as Text before entering or pasting the values, because changing a number to Text afterward does not restore zeros Excel has already removed. - Add one zero to existing values with a formula. Suppose the existing numbers are in A2:A20. In B2, enter
="0"&A2. For example, if A2 contains the number 123, the formula returns the text value 0123. Select B2 and drag the fill handle—the small square at the lower-right corner of the selection—down through the rows you need. Check a few results against the original values. The results are text, so they are suitable for codes but are not numeric values for arithmetic. - Keep blank source cells blank, if needed. The simple formula in the previous step returns a zero if its source cell is blank. If A2:A20 may contain empty cells, put this formula in B2 instead:
=IF(A2="","","0"&A2). With A2 blank, the result is blank; with A2 containing 123, the result is 0123. Fill the formula down as before. The formula still returns text for nonblank values. - Show a zero without changing the numeric value. Select the cells, press Ctrl+1, and open the Number tab in the Format Cells dialog. Choose Custom, enter
"0"0in the Type box, and select OK. This displays one literal zero before the number: numeric 123 appears as 0123, while the stored value remains 123. For a fixed five-character display, use00000instead: 123 appears as 00123 and 12345 appears as 12345. If a value is longer than the format width, Excel displays all its digits rather than cutting them off. - Check your result and decide whether to replace the source. Click a result cell and look at the formula bar. A custom format leaves the underlying numeric value unchanged; a formula result is text; an apostrophe-entered value is text. If you want to keep formula results but remove their link to the source, copy the results and use Home > Paste > Paste Values in a separate destination. Confirm that the pasted values look right before deleting or overwriting the originals.
In Excel for Mac, the same text-entry and formula methods work. To set a custom number format, select the cells and use Format > Cells, then choose the Number tab and Custom. Excel for the web also supports text entry, formulas, and custom formats, although the location of the number-format controls may differ from the Windows dialog. The display-only format does not turn a number into text in any of these versions.
Common Mistakes
Changing the cell to Text after Excel has removed the zero. If you entered 0123 into a General-formatted cell, Excel may have stored it as 123. Changing the format afterward cannot infer how many zeros you wanted. Re-enter the original value after formatting as Text, or rebuild it from a source that still contains the required information.
Confusing appearance with stored content. A custom format can make 123 look like 0123, but formulas and calculations still use 123. If you export or copy data into another system, the displayed zero may not travel with the underlying number. Use text when the zero is part of the identifier itself.
Using a fixed-width format when you mean to add exactly one zero. The format 00000 pads every number to five digits. It does not add just one zero in every case: 7 displays as 00007, and 12345 displays unchanged. For exactly one displayed prefix zero, use "0"0; for a text result with exactly one prefix zero, concatenate the zero with the value.
Expecting text results to behave like numbers. A formula that joins a zero to a number returns text. Sorting may put text and numbers in unexpected groups, and arithmetic may require conversion. This is normally appropriate for codes, but not for quantities or values that you need to calculate.
Tips
For a list of codes that must all have the same length, decide the required length first and use a matching custom format or a text-building method. For instance, if every code should display as six digits, the custom format 000000 pads shorter numbers to six displayed digits. It will not preserve meaningful letters or punctuation as part of a numeric value, so use Text for mixed-character codes.
Keep a copy of the original data until you know which method is right. A safe workflow is to put a formula in a neighboring column, check several short and long values, and then paste values into a new column if you need permanent text. This also makes it easier to spot a blank source cell or an unexpected value before replacing anything.
When importing a CSV or pasting data from another system, set the destination column to Text before importing or pasting if the leading zeros matter. If Excel has already interpreted the imported entries as numbers, formatting the cells as Text later will not bring the zeros back. If you have a reliable fixed width, you can reconstruct the display with a custom format or rebuild text from the original data.
For a number that must stay numeric, use a custom format rather than a formula. That keeps normal arithmetic and numeric sorting intact while changing how the cell is displayed. For codes that need to keep their exact characters when copied elsewhere, store them as text instead.
Troubleshooting
The zero disappears as soon as I press Enter. Excel is treating the entry as a number. Undo if possible, set the cell or column to Text using Home > Number Format > Text, and type the full value again. For a single entry, start it with an apostrophe, such as '0047. The apostrophe is a text-entry marker and is not shown as part of the cell value.
The formula shows 0 or an unexpected result. Confirm that the formula points to the correct source cell and that the source contains the value you expect. With ="0"&A2, a blank A2 produces a single zero; use =IF(A2="","","0"&A2) if blank rows should stay blank. If the source itself contains text or spaces, inspect it before deciding whether to clean or rebuild it.
The displayed zero is missing when I copy or export the data. You may have used a display-only custom format. The underlying number is still 123 even if the cell shows 0123. Use a text entry or formula result for a zero that must travel as part of the value, then copy and paste values if you need to remove the formula.
My IDs have different lengths, and the result is not right. Decide whether every ID needs the same total length or simply needs one extra zero at the start. A fixed format such as 00000 pads numbers to five displayed digits; a concatenation formula adds one zero regardless of the original length. If different IDs have different intended widths, a single fixed-width format may not suit them. Use text or handle those groups separately.
The formula result sorts differently from the source numbers. Concatenation creates text, while the source cells may be numeric. If numeric order matters and the zero is only for appearance, use a custom number format. If these are identifiers, text sorting may be acceptable; keep the whole identifier column consistently stored as text to avoid mixing types.
Frequently Asked Questions
How do I add a zero before a number in Excel without a formula?
For a new entry, type an apostrophe before the value or set the destination cells to Text before entering it. To keep an existing value numeric and only change its appearance, apply a custom number format such as "0"0.
Can I add a zero before an entire column?
Yes. Use a formula in a neighboring column and fill it down, or select the column and apply a custom number format. Use the formula when the zero must be part of a text result; use formatting when the values should remain numeric.
What is the difference between "0"0 and 00000?"0"0 displays one literal zero before the number, so 123 appears as 0123. 00000 displays at least five digits, so 123 appears as 00123. Both are formats, not changes to the stored number.
Will Excel keep the leading zero in a CSV file?
A CSV file does not store Excel’s cell formatting. A displayed zero from a custom format may therefore be absent from the exported data. If the zero is part of the code, convert or prepare the values as text and check the exported file before using it elsewhere.
Can I calculate with a value that starts with a zero?
If it is stored as a number with a custom format, yes: Excel calculates with the underlying number. If it is stored as text, it is best treated as an identifier. Convert text to a number only when the value is genuinely numeric and losing the leading zero is acceptable.
Why does Excel remove zeros from postal codes or account numbers?
Excel generally interprets entries made of digits as numbers, and numbers do not retain unnecessary leading zeros. Store codes as Text or use an appropriate display format when you need a numeric value to look padded. For data imported from elsewhere, confirm that the zeros were not removed before the values reached Excel.
Related
- Group Dates in Excel Filters: Steps, Fixes, and Tips
- Grocery List Excel Template: Free Options and Setup
- How to Password Protect an Excel Workbook in Windows
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Can Excel Read Outlook Emails, Contacts, and Calendar?Yes—Excel can work with Outlook data, but it does not automatically read your mailbox just because both apps…
- Meal Planner Excel Spreadsheet: Plan a Week of MealsYes. A meal planner Excel spreadsheet can organize a week’s meals, servings, prep time, and grocery needs in…
- Excel Not Responding? Close It Safely and Recover WorkIf Excel says “Not Responding,” first wait briefly if it may be calculating or opening a large file;…
- Excel Box and Whisker Plot: What Column Names MeanExcel box and whisker plots do not require special or reserved column names. Put each group of raw…
- Excel Shortcut to Expand a Column: AutoFit and MoreIn Excel for Microsoft 365 on Windows, select a cell in the column and press Alt, H, O,…
- Excel Table: How to Flip Rows and ColumnsTo flip an Excel table so its rows become columns and its columns become rows, copy the range,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.