Have you ever typed a number like 01234 into Excel, only to have Excel automatically change it to 1234? This happens because Excel treats it as a number and removes the zero at the beginning. It’s a common issue when working with ZIP codes, ID numbers, product codes, and other values that should always display a fixed number of digits.

In this tutorial, you’ll learn three different ways to keep the zero at the beginning of a number in Microsoft Excel. We’ll cover built-in number formatting, the TEXT function, and custom number formats so you can choose the method that works best for your data.

What You’ll Learn

• Why Excel removes zeros at the beginning of numbers
• What a leading zero is
• How to keep the zero using Excel’s built-in ZIP Code format
• How to use the TEXT function to display zeros at the beginning of numbers
• How to create your own custom number formats
• Which method is best for different situations

Common Uses

• ZIP codes
• ZIP+4 codes
• Employee IDs
• Customer account numbers
• Product or inventory numbers
• Membership IDs
• Student IDs
• International phone numbers
• Any fixed-length numbering system

Requirements / Compatibility

This tutorial was originally recorded in 2020, and all three methods continue to work in current versions of Microsoft Excel, including:

• Microsoft 365
• Excel 2024
• Excel 2021
• Excel 2019
• Excel 2016
• Excel for Mac
• Excel for the web*

*The built-in ZIP Code number format may vary slightly depending on your regional settings, but Custom Number Formats and the TEXT function are widely supported.

Practice & Resources (Reference Guide)

You can follow along using any worksheet that contains numbers where Excel has removed the zero at the beginning.

The examples in this tutorial use five-digit ZIP codes, but the same methods also work for employee IDs, account numbers, product codes, and other numbers that should always display the same number of digits.

Need a quick refresher later? This tutorial includes a printable reference guide that summarizes the steps and key concepts covered in the lesson.

Click the button below to view the resources available for this tutorial.

Video Tutorial

Breaking Down the Concept

If you type a number like 01234 into Excel, you’ll probably notice it immediately changes to 1234. That’s because Excel assumes you’re entering a number, and numbers don’t normally keep zeros at the beginning.

Those beginning zeros are called leading zeros.

Fortunately, you don’t need to type apostrophes or manually re-enter the zeros. Excel provides several ways to display them while keeping your data organized.

Method 1: Special Number Format

Excel includes a built-in ZIP Code format that automatically displays five-digit ZIP codes correctly.

Best for:

• Standard U.S. ZIP codes
• Keeping values numeric
• Sorting and calculations

Method 2: TEXT Function

=TEXT(A1,"00000")

The TEXT function converts the number into formatted text by displaying the correct number of digits.

Best for:

• Exporting formatted values
• Combining numbers with text
• Flexible formatting

Method 3: Custom Number Format

Examples:

00000

or

00-000

Custom number formats let you create your own display patterns for IDs, account numbers, phone numbers, and many other numbering systems.

Best for:

• Employee IDs
• Product numbers
• Custom numbering systems
• International formats

Which Method Should You Use?

Step-by-Step Instructions

Method 1: Format Cells (Best for Standard ZIP Codes)

1. Select the column/cells containing your numbers.
2. Right-click and choose Format Cells (or go to the Home tab → click the small arrow in the corner of the Number group).
3. Select Special from the category list.
4. Choose Zip Code (or Zip Code +4 if your numbers include the extra 4 digits).
5. Click OK. The leading zero will now display, and the cell still holds a real number underneath — the formatting just changes how it’s shown.

This is the easiest and most reliable method, since the underlying value stays numeric (which matters if you ever need to sort, filter, or reference it in calculations).

Method 2: TEXT formula (more flexible, but creates text)

1. Click an empty cell next to your number (e.g., if your number is in H1, click I1).
2. Go to the Formulas tab → Insert Function, search for TEXT, and select it.
3. For Value, click on the cell with your number (e.g., H1).
4. For Format_text, type five zeros in quotes: “00000” (adjust the number of zeros to match how many digits you need).
5. Click OK. The result will show your number padded with leading zeros.
6. To apply this down a column, hover over the bottom-right corner of the cell until you see a black plus sign, then click and drag down.

Caveat: this method converts the result into text, not a number. If you copy and paste it as values elsewhere, you may see error indicators (Excel flagging it as “number stored as text”). Method 1 avoids this issue entirely.

Method 3: Create a Custom Number Format (for non-zip-code patterns)

1. Select the cells, open Format Cells again.
2. Choose Custom instead of Special.
3. In the Type box (replacing “General”), build your own pattern using 0 for required digits — e.g., 00-000 for a two-digit, dash, three-digit format.
4. Click OK.

This gives you the most control but requires manually designing the format string.

Additional Tips

• If you need the value to remain a number, use Special or Custom Number Formatting instead of the TEXT function.
• If you copy a TEXT formula and Paste Values, Excel may display a “Number Stored as Text” warning. This is expected because the result is now text.
• Custom Number Formats work best when every value should contain the same number of digits.
• If you’re importing data from another system, consider applying the formatting after the import instead of manually editing each value.

Related Features

• Custom Number Formatting
• TEXT Function
• TEXTJOIN
• CONCAT
• Flash Fill
• Text to Columns

Related

How to Remove Extra Spaces in Excel

Learn how to remove extra spaces from text in Microsoft Excel using the TRIM function. This beginner-friendly tutorial explains what TRIM removes, when to use it, and why it’s one of the most useful functions for cleaning imported or inconsistent data.

How to Hide Zero Values (0s) in Excel

Learn two easy ways to hide zero values in Microsoft Excel without changing your formulas. This step-by-step tutorial demonstrates both the built-in worksheet option and a Conditional Formatting method to create cleaner spreadsheets, reports, and templates. Includes updated instructions and screenshots for current versions of Excel.

How to Create an Order Form with Drop-Down Menus in Microsoft Excel

Learn how to create a professional order form in Microsoft Excel using drop-down menus, Data Validation, and named ranges. This step-by-step tutorial also covers formatting dates, ZIP codes, and credit card fields to build cleaner, more accurate spreadsheets for real-world business use.

Stay Updated

There are two easy ways to get notified when new tutorials are published:

Subscribe on YouTube for new video tutorials: @SimpleSoftwareTutorials

or enter your email below to receive new blog posts directly in your inbox: