How to add leading zeros in Excel

Why Excel removes leading zeros

When you type 02134, Excel sees a number, and as a number 02134 is just 2134. It stores 2134 and the zero is gone. The same happens when you open a CSV file by double-clicking it, which is why ZIP codes from New England and New Jersey arrive as 4 digits and ZIP 00501 shows as 501.

So the real question is whether the value is a quantity or an identifier. ZIP codes, employee numbers, phone numbers and account numbers are identifiers. You never add them up, so storing them as text is fine, and often better.

Option 1: a custom number format (stays a number)

A custom format changes only how the cell looks. The cell still holds 2134, so it sorts and calculates as a number, but it displays as 02134. Each 0 in the format is a digit that is always shown, padded with zeros if needed.

For US ZIP codes, Format Cells > Special > Zip Code applies the same 00000 format for you. Use 000-00-0000 or (000) 000-0000 style codes for other fixed layouts.

1. Select the cells.
2. Press Ctrl+1 (or right-click > Format Cells).
3. On the Number tab, choose Custom.
4. In the Type box, enter one 0 per digit, such as 00000 for a 5-digit ZIP.
5. Click OK.

Option 2: TEXT in a formula (real zeros, as text)

TEXT applies the same kind of format code inside a formula, and returns the result as text. The zeros are now part of the value, so they survive copying to another program, a mail merge, a CSV export, or joining with other text.

This is the method to use when another system needs the code with zeros, or when you build a longer ID from the number.

Build an ID with a prefix

Joining a number to text drops its formatting, so ="EMP-"&B2 gives EMP-42. Pad it with TEXT inside the join to get a fixed-width ID that sorts properly.

Pad codes that are already text

TEXT expects a number. Codes that are already text, like the ones in column C, still work because TEXT converts digit-only text, but codes with letters come back unchanged. A method that works on anything: stick zeros on the front and keep the right-most characters.

Put at least as many zeros in the string as the width you want. RIGHT then keeps exactly that many characters from the end.

Typing and importing zeros that stick

To type a few codes by hand, start with an apostrophe: '02134. The apostrophe is not shown or stored in the value, and the cell becomes text. For a whole column you will type into, format it as Text first (Home > Number Format > Text). Formatting after typing does not bring zeros back.

For CSV files, do not double-click them. Use Data > From Text/CSV, click Transform Data, select the column and set Data Type to Text. In recent Excel for Microsoft 365 versions you can also turn off the automatic conversion: File > Options > Data > Automatic data conversion, and clear the option to remove leading zeros.