Excel Tutorial: Adding Leading Zeros to Numbers - Step-by-Step Guide

Excel Tutorial: Adding Leading Zeros to Numbers - Step-by-Step Guide

Charles Lv12

Excel Tutorial: Adding Leading Zeros to Numbers - Step-by-Step Guide

When entering numbers into Excel, zeros at the beginning of the number are removed. This can be an issue when entering phone numbers and IDs. In this article, we will cover ways to solve this problem and keep the leading zeros.

Keep the Leading Zero as you Type

If you wanted to ensure that the leading zero is kept when typing, enter a single quote before you type the number.

Sinlge quote before phone number

This instructs Excel to store the value as text and not as a number.

When you press “Enter” to confirm, a green triangle is shown in the top left corner of the cell. Excel is checking that you intended to do that or if you want to convert it to a number.

Click the diamond icon to display a list of actions. Select “Ignore Error’ to proceed and store the number as text.

Phone number stored as text in Excel

The green triangle should then disappear.

Apply Formatting to Keep the Zero

It is quick and simple to apply text formatting as you type, but it is even more efficient to do it in advance.

Select the range of cells you want to format as text. Next, click the “Home” tab, select the list arrow in the Number group, and choose “Text.”

Format a range as text in advance

The values you enter into this formatted range will now automatically be stored as text, and leading zeros preserved.

ZoneAlarm Extreme Security NextGen

Using Custom Formatting to Keep it Numeric

The previous two options are great and sufficient for most needs. But what if you needed it as a number because you are to perform some calculations on it?

For example, maybe you have an ID number for invoices you have in a list. These ID numbers are exactly five characters in length for consistency such as 00055 and 03116.

To perform basic calculations such as adding or subtracting one to increment the invoice number automatically, you would need it stored as a number to perform such a calculation.

Select the range of cells you want to format. Right-click the selected range and click “Format Cells.”

Format Cells from the shortcut menu

From the “Number” tab, select “Custom” in the Category list and enter 00000 into the Type field.

Setting up a custom number formatting

PCDJ Karaoki is the complete professional karaoke software designed for KJs and karaoke venues. Karaoki includes an advanced automatic singer rotation list with singer history, key control, news ticker, next singers screen, a song book exporter and printer, a jukebox background music player and many other features designed so you can host karaoke shows faster and easier!
PCDJ Karaoki (WINDOWS ONLY Professional Karaoke Software - 3 Activations)

Entering the five zeros forces a fixed-length number format. If just three numbers are entered into the cell, two extra zeros are automatically added to the beginning of the number.

Fixed length number formats

/a>

You can play around with custom number formatting to get the exact format you require.


Excel’s ability to format cells is an excellent tool to provide consistent formatting of phone numbers, credit card numbers, and IDs—especially when the data is entered by multiple people.