How to Add Leading Zeros in Excel (5 Easy Ways + VBA)

Have tried entering a code prefixed with a bunch of zeros only to find that not one zero made it through? That's Excel's manhandling for you. Looking at things from Excel's eyes, if you're trying to enter a code, let's say 0000990, Excel will process this as a number starting from the first non-zero number and return 990.

As a standalone regular number, 990 doesn't need some zeros behind it and for that reasoning, Excel would be right because Excel doesn't have a default setting for accepting such codes, only proper numbering.

Adding a symbol or space character can help keep the zeros but that's not the case with a period. Have a look below at how Excel treats characters and spaces or the lack of them:

A quick fix would be to lead the number with an apostrophe, like row 3 ahead:

But the apostrophe changes number to text. You may need numbers with leading zeros as product or employee codes, transaction IDs, card or role numbers. If you must stay clear of using characters, we have more adaptable methods for keeping the zeros in the lead.

In this tutorial, you will learn how to add leading zeros to a number using Text and Custom formats, the CONCATENATE, REPT, and TEXT functions and also a custom function usable via VBA. It all sounds so fancy for keeping mere zeros but all the methods take little time and only a few steps.

If you're unsure of the implications of these methods, here's what you need to know:

• Number format: keeps the numbers compatible with calculations and numeric functions (e.g. SUM function) but makes them incompatible with text functions.
• Text format: makes the values compatible with lookup functions (e.g. VLOOKUP function) but renders them incompatible with numeric functions.

A little heads up, the Custom Format method is the only method that keeps the numeric value of the numbers and doesn't change numbers to text. For maintaining the values as numbers, head directly to that section.

Example

As our case example of this tutorial, we'll be adding leading zeros to 3- and 4-digit numbers to return a 7-digit club membership card number.

Let's get adding!

Recommended Reading: How to Remove Leading Zeros In Excel

Method #1 – Converting the Number Into Text

This is the most manually-led method. If the numbers are to be inputted manually (remember the help of Flash Fill for such things), the format of the target cells can be changed to the Text format. This way, Excel will accept all the leading zeros as text instead of numbers and won't eliminate them.

This is how to convert numbers into text with the Text format:

• Select the target cells for the numbers to be entered. The default format of all cells is General.

• Click on the Number Format bar in the Home tab's Number section. Select the Text format from the menu.

• The format of the selected cells will have changed from General to Text, as seen in the Number Format bar.
• Enter the numbers including the leading zeros in the Text format cells.

Now the leading zeros will be accepted as text and won't be eliminated from the cell value but will be displayed with an error for numbers stored as text.

The difference in the Text format and number formats can be noted by the alignment of the cell values (Text format is left -aligned and number formats are right-aligned).

Now for the errors, that doesn't look right, does it? Good that we can get rid of them. To remove the errors:

• Select all the cells with the error.
• Hover the cursor over the exclamation point that appears after selecting the cells with the error.
• A drop arrow will appear. Click on it and select Ignore Error from the menu.

That should ignore the error and remove all the green triangles from the cells:

This method is restrictive to functions that take numbers as arguments.

Method #2 – Using CONCATENATE Function

This method adds a fixed number of leading zeros.

The CONCATENATE function joins multiple text strings into a single text string. This works for our case example for joining leading zeros and the card numbers in column B. The two values will be concatenated as a single text string.

Have the numbers ready in one column as we have in column B. Then, use this formula to add leading zeros to the numbers in column B:

The first argument is the leading zeros enclosed in double-quotes because if not for the double quotes, no matter how many zeros you add, they will be reduced to a single digit zero. That makes the first part of the string. The second part of the text string is the card number in cell B3.

Below are the results of using the CONCATENATE function to add leading zeros to our card numbers:

Note: The last two values are 4 digits instead of 3 digits like the rest of the card numbers. For these two instances, the formula will have to be edited to add "000" instead of "0000".

This care only needs to be taken with the CONCATENATE function and the Text format. All the other following methods in this guide will automatically take care of the total number of characters.

Method #3 – Using REPT Function

For alphanumeric data, this method is your best bet. The REPT function repeats text, a given number of times. That works nicely to repeat the leading zeros. Simply, this formula should do the trick:

This will repeat the zero 4 times and join that to the number in B3 and that would give us the required card number 0000990. That would still leave us with the problem of the extra zero in the last two card numbers. Hence, to end up with the result of a 7-digit card number every time, we include the LEN function which returns the number of characters in a text string.

Use the formula below with the REPT and LEN functions to add leading zeros to result in a 7-digit number:

The REPT function will add zeros to our card numbers listed in column B but how many will it add? That is defined in the second argument of the REPT function. We want a 7-digit number so the total characters need to be 7. LEN(B3) will count the total characters of cell B3 i.e. 3.

7-3=4

4 zeros are to be added to B3. Along with the 4 zeros, we need the card number in B3 which has been joined to the zeros using the ampersand (&) operator. The result is 0000990.

This will also work for the last two card numbers as the formula will work out the number of zeros to add in this way:

Total characters are 7 and LEN(B13) is 4.

7-4=3

3 leading zeroes will be added to B13.

Method #4 – Using TEXT Function

The TEXT function converts a value to text in a given number format. This works just like using custom formats (explained in the next method) but in a function form and while custom number formats keep the numeric values, the TEXT function will convert the numeric values to text, making them unusable in numeric functions.

This is the formula we are going to use to add leading zeros to make the values in column B into 7-digit numbers:

Firstly, we refer the cell containing the number we want to format as a text i.e. cell B3. Then we have added the format enclosed in double-quotes. For a 7-digit display, the format given to the TEXT function is 7 zeros wrapped in double quotes.

This will accordingly deal with the 3- and 4-digit card numbers to make them 7 digits:

Method #5 – Using Custom Format

This method is the only one that maintains the numeric value even if you need characters (e.g. hyphens in codes, parentheses in phone numbers) added.

Since we are using a custom number format, only the display of cell changes, keeping the numeric value intact.

Custom number formats allow you to add characters without changing the numbers to text. You can explore the preset options in the Special and Custom section of the Format Cells dialog box or enter a format of your own, which is what we are going to detail below.

These are the steps to add leading zeros with Custom format.

• Select the cells with the numbers that you want to add the leading zeros to.
• To open the Format Cells dialog box, click on the dialog launcher (marked below) of the Number group in the Home Alternatively, press the Ctrl + 1 keys.

• From the end of the left pane in the Format Cells dialog box, select the Custom

• In the Type: area which is showing General right now, type the number of characters you want in the values as zeros. E.g. we want 7 characters and have typed 7 zeros in the provided field.

• Click on the OK command button when done.

The format applied will add as many zeros as it takes to make the values in column B a total of 7 characters:

Method #6 – Using Custom User Defined Function in VBA

Leading zeros can also be added using a custom function and that function can be created using VBA. Let's see the detailed steps for using a custom function in VBA to add leading zeros:

• Select the Developer Click on the VB button in the Code group to open the VB editor. If your Developer tab is hidden, you can press the Alt + F11 keys.

• Select the Insert tab and then select the Module option to launch a Module window for entering the code for the custom function.

• Enter the following code in the field of the Module window:

Function AddLeadingZeroes(ref As Range, Length As Integer)
Dim i As Integer
Dim Result As String
Dim StrLen As Integer
StrLen = Len(ref)
For i = 1 To Length
 If i <= StrLen Then
 Result = Result & Mid(ref, i, 1)
 Else
 Result = "0" & Result
 End If
Next i
AddLeadingZeroes = Result
End Function

The code has already defined that the value to be added is zero and is to be added to the start. Now all the function will require is the reference of the cell with the number and the number of characters you need the value to be.

• Exit the VBA
• In a column next to the original numbers, enter this formula (as followed from the code):

While typing the formula, note that you can see the name of the function in the function AutoComplete. Tag the reference of the first cell with the numbers, cell B3 for our example. Then define the total number of characters for the string.

Here are the results produced by this function:

And there we have led and trailed our way to a conclusion on adding leading zeros in Excel. Wasn't that so simple?  Now adding your requirement of zeros shouldn't be a sweat at all. We have lots more in store to make your Excel experience smooth. Coming soon to a spreadsheet near you!