Buy Now

How to Add Leading Zeros in Microsoft Excel

Apr 27, 2022
Learn how to add zeros in Excel to store your SSN, phone numbers, and other numbers that have leading zeros

Excel contains many useful and valuable features for the user. Still, it has a lot of quirks and issues that could be annoying to someone who isn't that well-versed in Excel.

For example, the zeros in Excel could prove to be a challenging number to deal with. Microsoft Excel automatically removes zeros written on the left, or leading zeros in a cell, whenever you find yourself having to enter zeros in cells in a specific numbering system.

This could be the case if you maintain records in Excel, such as Serial numbers, Social Security Numbers, format codes, postal codes, account numbers, employee ids, zip codes or phone numbers starting with zeros.

In this article, you can learn how to control leading zeros in spreadsheets and switch between different modes based on your current work. You can also follow us on YouTube for more amazing tips, tricks and template tutorials.

 

How to Add Leading zeros in Excel?

Given that you may not like adding leading zeros in Excel manually, there are many ways to add leading zeros in Excel using different Excel features automatically.

We will learn all these other methods in the following few lines in detail:

If you type some leading zeros into the cells as shown in the screenshot below.

Now, Press enter, and you will find that the numbers are entirely gone because Excel does not automatically read the leading zeros at all.

So, now the leading zeros have vanished and I assume your working day has got more challenging.

There are different ways to add leading zeros in Excel.

Let's find out the methods that fit your needs.

Method 1: How to add leading Zeros by using the Format Cells Dialog Box

To apply this custom format method adds zeros follow the next few steps:

To facilitate your work tasks to add leading zeros, it may be good for you to know the number of digits in each cell by a simple formula.

Step One:

Write these LEN functions in a blank cell to know the number of digits:

=LEN(A1)

You will know the importance of the above formula to add leading zeros in excel later in this method.

Note:

The formula will be written in the cell, and it will also appear in the excel text function string above or the formula bar. See the red box in the screenshot.

After writing the above formula in the cell, press Enter and the number of digits written in the numeric formula will appear in the cell. As shown below, see the screenshot.

Note:

You have to change the column number and letter each time you type the other formulas according to the place of the cell you want to know how many digits it has.

For Example, The numeric formulas will be =LEN(A1) if you are targeting to know the numbers of digits in the cell located in column A, row 1.

And it will be like the following formula =LEN(B2) if you are targeting to know the numbers of digits in the cell located in column B, row 2. etc.

write the same formula in all the cells you need to know their number of digits in the text string.

Step Two:

Select all the cells you want to edit and add leading zeros to them.

You can do this by dragging the cursor or holding down the transform file on the keyboard while clicking with the mouse. As shown below, See the screenshots.

You can select adjacent cells by clicking and the keyboard shortcuts as follows:

Click on the first cell in the cells field with the left mouse button,

Then press the Shift button on the keyboard,

Finally, Click on the last cell you want to access in the cells field while holding down the Shift button.

Step Three:

Right-click on your selection, then choose Format Cells.

Alternatively, you can use the keyboard shortcut by pressing Ctrl+1 to open the format cells window faster.

Step Four:

The Format Cells dialog box will appear. Select Custom Number Format or the Number tab. As shown in the screenshot.

Step Five:

Select Custom Format Cells.

Step Six:

Create the following custom number format by adding as many zeros as the number you got from the digits.

Step Seven:

You can add zeros in the custom format code by replacing the word General type box or the text string with zeros.

Note:

The format code or the number of zeros here is variable length numbers. It is selected according to the number obtained by using the formula. You can add five zeros or six or more.

In our example, we added the fixed-length numbers or the fixed number of zeros which is 10 Zeros to add only one leading zeros or to insert fixed numbers of zeros before a number.

Step Eight:

Press Ok to add the leading zeros.

Here you are, the final result of adding leading zeros in the custom format.

Note here this method didn't change the numeric format, it only shows the fixed length of the pre-added zeros without messing with the cell format. See the screenshot below.

There is one problem with this method that it only works later. You need to set the cell format to text values before typing any text. If you try to do this after that, the zeros will disappear, and you will need to re-write them.

Note:

Selecting a cell with formatting will still show the original number in the formula bar. They only appear in the worksheet with the leading zero formats. It's only the way it's displayed that is changed.

This also means if you copy and paste the data as values, you will lose the leading zeros.

It might help if you see How to Change Currency in Excel

Method 2: How to Add Leading Zeros by Adding an Apostrophe

The LEN functions method is not the only way to keep the leading zeros. But adding a leading apostrophe A quick and easy way to get the same result is to use an apostrophe or single quotation mark or the magic apostrophe. Doing so will prevent Excel from automatically converting your number.

You can keep the leading zeros in the dataset available in Excel by this quotation mark (') method.

For example, if you have phone numbers or any numeric values you need to keep their arithmetic calculations right. To do this, follow these few steps for adding leading zeros:

Step One:

Write this apostrophe (') at the beginning of the number or the numerical value. As shown below, see the screenshot.

Step Two:

Press Enter, and you'll keep the leading zeros in this specific Cell. See the screenshot.

Here, as you see in the above example, this method will not change the number format or the custom number formatting.

It only shows the leading zeros without messing with the cell format.

Note:

You might see a small green triangle (An Error indicator) in the top-left corner of the cell, and when you select that cell.

This will show you a warning sign indicating that you have stored the number as text.

The leading apostrophe will appear only in the formula bar but not in the cell.

Also, Note:

This method may achieve the desire of keeping the leading zeros, but it is neither practical nor productive.

As you may take a lot of time to do this in each separate cell.

It may be a proper method for you when you import data or numbers for the first time yourself; so, in this case, the quotation mark method will not take much time and maybe it is the easiest to follow.

Method 3: How to Add Leading Zeros by Using the Custom Format as a Text Value

This method is a very productive one and it is somewhat similar to the first method in some steps. So, How do you convert numbers to text in excel without losing zeros?

Step One:

Select all the empty cells you want to enter and add leading zeros to them.

You can do this by dragging the cursor or holding down the transform file on the keyboard while clicking with the mouse. As shown below, See the screenshots.

You can select adjacent cells by clicking and the keyboard shortcuts as follows:

Click on the first cell in the cells field with the left mouse button,

Then press the Shift button on the keyboard,

Finally, Click on the last cell you want to access in the cells field while holding down the Shift button.

Step two:

Right-click on your selection, then choose Format Cells.

Alternatively, you can use the keyboard shortcut by pressing Ctrl+1 to open the Format Cells dialog box faster.

Step Three:

The Format Cells dialog box will appear. Select Custom Number Format or the Number tab. As shown in the screenshot.

Step Four:

Select the Text Format. As shown below in the screenshot.

Step Five:

Press OK, to get the text values in the chosen cells. See the screenshot.

This method will make excel treats the number format as a text string, not a number.

If you entered a number with leading zeros, that will force excel to show the numbers as not a number.

Step Six:

Write any number leading zeros and press Enter.

Here, See the below screenshot, you will find the leading zeros appears in the cell format.

Note:

You might see a small green triangle (An Error indicator) in the top-left corner of the cell, and when you select that cell.

This will show you a warning sign indicating that you have stored the number as text.

Method 4: How to Add Leading Zeros by Using The Text Function

The text function is one of the best tools in Excel to keep the leading zeros. This is more or less similar to setting your own custom number formatting in the Format Cells dialog box.

So, let's learn what the text function does in the following few lines:

The text function enables you to refer to text or numbers, then the selecting of the format for the output.

This function uses the syntax "=text(value, format)."

The value can be any cell reference or number, and the format follows the same structure as the custom number configuration, except that the formatting is enclosed in the quotation mark.

The value function converts the value in A1 to a number.

As an example, the following formula converts the number in cell A1 into an 11-digit phone number:

= text(A1, "0000-000-0000")

You can use this method when you want to copy and paste numerical data into other cells while adding leading zeros to the new cell.

Note that:

you will have two copies of the data; The original version that does not include the leading zeros and the new version that contains the actual number in addition to the leading zeros.

So, How you can use the text function to show the leading zeros?

Follow these few steps to keep the leading zeros in a text format by using this TEXT formula:

Step One:

Write this TEXT formula = text(A1, "0000-000-0000") in any empty cell. As shown in the screenshot.

Note:

You will find the text formula in the cell and the formula bar. As shown in the text string inside the red box. See the screenshot below.

Step Two:

Press Enter.

You will find the exact number in the original cell plus the leading zeros. See the screenshot below.

For more great Excel tips and tricks, check out our blogpost Excel is Fun

Method 5: Insert the Same Number of Leading Zeros Into Numbers Using The Concatenate Function

The custom number formats and the text function specify a certain number of digits. Still, the number of leading zeros varies with the number of digits entered, excluding the leading zeros. However, the Concatenate function allows you to add a specified number of leading zeros, no matter how many digits are included in the source cell. Use the structure "=Concatenate([zeros], A1)" and enter the zeros enclosed in quotes. For example, to add three zeros to the text or number in A1, use the formula =CONCATENATE("000",A1)

To add or insert the same number of leading zeros into numbers using the Concatenate function.

If you want to enter a certain number of leading zeros in each number, say three leading zeros for each number, you should try the concatenation function.

Step One:

Enter the formula =CONCATENATE("000",A1) in a blank cell.

Step Two:

Press Enter.

Three zeros have been added before the numbers according to the prefixed zeros. See the screenshot above.

The formula bar will show the added formula while the numbers appear in the cell.

In this article, we've given you several ways to get the same result, and as the above methods show, there are plenty of options available to you if you're looking for a simple way to add leading zeros to cells in Excel.

If you find out that these methods are too hard for you to use, take up more of your work, or don't have the luxury of using them because you're short on time, you can learn how to use Excel's features to process your data at scale using the ready-to-use template that provided by Simple Sheet. Excel is estimated to be used by more than 1.2 billion people worldwide in over 140 countries. With such widespread use comes the need to accommodate and process data specific to different countries. So you can see the Simple Sheet Ready-to-use Template Catalog Here.

 

 

 

 

 

81% of companies across the use Microsoft Excel. 

Mastering Excel can increase your earnings up to 4x over the course of your career!

Bottom Line: It pays to master Excel. Take our world-class Excel University and take your spreadsheet skills to the next level.

Buy Now

Get 5 FREE Excel Templates and Trainings

What email do you want us to send your access to?

We hate SPAM. We will never sell your information, for any reason.