Leading Zeros: How To Add Them To Numbers In Excel
Office Mastery
S H A R E
Assign a 'primary' menu

How To Add Leading Zeroes To Numbers In Excel

Leading Zeros in Excel

BY JASON MORRELL

Have you ever typed a number with one or more leading zeros, like 00123 and watched as Excel drops the leading zeros as soon as you press Enter.

Frustrating, isn’t it!

So how do you fix it?

There are 2 methods. One is better than the other for a quick set up and easy maintenance. There is also a third method which combines elements from each of the first two.

Cheat Sheet - How to display leading zeroes in Excel

FEATURE DOWNLOAD

Grab the free Leading Zeroes
cheat sheet

Method 1: Format the cells as text

1.       Before you enter any data, select the cells where the data is to be entered.

2.      Press CTRL 1. This is a keyboard shortcut to display the Format Cells dialog.

3.      On the Number tab, choose the TEXT category.

4.      Close the dialog.

Now, when you enter your data with leading zeros, the leading zeros remain and do not disappear when you press Enter.

A couple of things to watch out for:

1.       Text is left-aligned by default so you may need to re-align the data.

2.      If your data has already been entered (or downloaded), switching to a text format will not fix the data - there is a partial fix for this which is outlined in Method 3.

Method 2: Use custom formatting

1.       Select the cell range where the data will be entered (or has already been entered).

2.      Press CTRL 1 to display the Format Cells dialog.

3.      In the Number tab, choose the CUSTOM category.

4.     Custom formatting works with a system of place holders. For numbers, the primary place holders are:

  • '#' which represents an optional digit 0-9,
  • '0' (zero) which represents a mandatory digit 0-9 (this is what we need for leading zeros)
  • Any punctuation you add is displayed as-is.

5.       If you have a 5 digit product code, enter 00000 into the TYPE box. This will convert 123 to 00123, 1 to 00001 etc.

6.      If you have a phone number with a 4-digit area code followed by 2 sets of 4 digits, enter (0000) 000-000 into the TYPE box. This will convert 0123456789 to (0123) 456-789.

7.      Close the dialog.

Create a custom format

Figure 01: Create a custom format for an Australian mobile number

The new custom format will now be applied.

Phone numbers will now have the punctuation and spacing. For example, enter 0123456789 into a custom-formatted phone number cell (see above) and press Enter. It will automatically reformat to (0123) 456–789.

Numbers that previously dropped the leading zeros will now display the leading zeros. For example, enter 123 into a custom-formatted number cell (see above) and press Enter. The leading zero will be added automatically to display 00123.

Method 3: Expert Tip - Combine methods 1 and 2

If you import data into Excel that was exported from another program or website, the data is often formatted entirely as text.

Numbers stored as text

Figure 02: Numbers stored as text

You can click the smart icon (see above), then choose Convert To Number but you still don’t have the leading zeroes!

An easy way to convert a lot of ‘text’ numbers to ‘numeric’ numbers (if that makes sense) and add leading zeroes is to follow this process (assume the ‘text’ numbers are in column A):

Numbers start in text format

Figure 03: Numbers start in text format

1.       Insert a blank column, if necessary.

2.      In cell B1 type =A1*1 and press Enter. The result is the same number but in numeric format (notice that it is right-aligned).

Formula that converts 'text' number to 'numeric' number

Figure 04: Formula that converts 'text' number to 'numeric' number

3.       Copy the formula down.

4.      Select column B and assign the custom format to add the leading zeros as described above.

Select the entire column and set a custom format

Figure 05: Select the entire column and set a custom format

5.       With column B still selected, COPY the data then PASTE SPECIAL as VALUES (using the right-click menu) to column B to overwrite the values, or PASTE SPECIAL directly over the original ‘text’ numbers in column A if your prefer.

Select the column of data, COPY, then PASTE SPECIAL the VALUES over the same cells

Figure 06: Select the column of data, COPY, then PASTE SPECIAL the VALUES over the same cells

6.       Delete column B as it is now redundant.

I hope that gets you out of a fix. 

What do you think?

Watch the video (over the shoulder demo)

To see this technique demonstrated with additional examples, check out this Leading Zeroes video.

Click to watch video

What next?

If this post helped, or you have a question, drop a quick comment below. I always love to hear from my readers. Here's to your learning and success. Enjoy the rest of your day.

Jason Morrell blog signature
Jason Morrell, Office Mastery

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

>