Excel VBA to Format Number with Leading Zero

Have you ever noticed that when you want to add a zero in front of any number, Excel automatically discards that? So, how can we add a zero in front of a number when we need it? The answer is we have to apply some special formulas or run a VBA to format numbers with leading zero in Excel. If you are interested to know the ways of doing that, just go through the article and you will get some very interesting ways. Stay connected.

How to Launch VBA Editor in Excel

In this section, we are going to demonstrate how to launch the VBA Editor & create a VBA module in Excel. First, you need the Developer tab to display on your ribbon. If you don’t have that, you can look for it in how to enable the Developer tab on your ribbon.

• First, we go to the Developer tab.
• Then we will select Visual Basic.

Then a new window will pop up. Then we are going to follow these steps,

• First, we will select Insert.
• Then we are going to select Module.
• A new Module will be created.

How to Format Number with Leading Zero in VBA: 4 Ways

When you write a number in Excel with a leading zero, it automatically removes the zero. To add zero, we have to perform some extra work. In the following section, you will find some ways to add a leading zero in a number with VBA code.

1. Leading Zeros by Creating a Custom Function

It is a very smart way to use a custom function in VBA to format numbers with a leading zero. Writing a function in VBA is almost the same as writing normal code in VBA. We have named our code “LeadingZeroes”. Like other Excel built-in functions, you will find this function as well.

It has 2 arguments: the first one is the range of cells of which numbers you want to format and the second one is how many numbers of 0 you want in the result. The code regarding the function is given below.

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

When you write down the code in VBA Editor, it looks like the image below.

StrLen = Len(ref)

For i = 1 To Length
  If i <= StrLen Then
     Output = Output & Mid(ref, i, 1)

 Else
     Output = "0" & Output

LeadingZeroes = Output

Execution and Output

Now, we want to see how the code works. Follow the steps below and see the output easily!

First, give an equal sign to the cell where you want to see the output.

Then, select the name of our function(LeadingZeroes) from the option.

Finally, select the desired input and give the expected length of the output. We have chosen cell B4 and gave the desired length “5”.

Our output looks like the image below.

Read More: How to Use Excel VBA to Format Number in Decimal Places

2. Add Leading Zero by Ampersand Operator

In the VBA code, we may use the Ampersand operator to add a leading zero to a number in the worksheet. However, in the code, you have to declare the target sheet properly. Otherwise, the code may not work. The code is given below.

Sub Add_Leading_zero()
Dim ws As Worksheet 'variables declaring
Set ws = Worksheets("Sheet2")
'add a leading zero to a number
ws.Range("C4") = "'0" & ws.Range("B4")
End Sub

If you copy the above code in your VBA Editor, it looks like the image below.

Set ws = Worksheets("Sheet2")

ws.Range("C4") = "'0" & ws.Range("B4")

Execution of Code and Viewing Output

Before we run the code, cell C4 is empty. B4 contains an integer number with no leading zero.

Just after we run the code, C4 contains a number with leading zero like the image below.

3. Using Number Format to Add Leading Zero

We can add a leading zero to a certain number in a certain cell by using the number format option using VBA. Unlike the ampersand method, it does not add a single 0. Rather it adds 0 based on the size of the number. The code is given below.

Sub leading_zero_by_numberformat()
Dim ws As Worksheet
Set ws = Worksheets("Sheet3")
ws.Range("C4").NumberFormat = "000000"
ws.Range("C4") = ws.Range("B4")
End Sub

When you write the code in the VBA Editor, it looks like the following image.

ws.Range("C4").NumberFormat = "000000"

ws.Range("C4") = ws.Range("B4")

Execution of Code and Viewing Output

First, we have given input in cell B4.

Then we run the code in the Editor. Cell C4 gets a value like the image below.

Read More: Excel VBA: Format Percentage to 2 Decimal Places

4. Format all Numbers in a Column to Leading Zero

Suppose you have a lot of data in a single column. Then it would be very difficult to format one by one. How would it be if there is a single code that does all the formatting at once? Very helpful, right?

If you wish to do that just look at the code below. It will help you a lot.

Sub Column_Formatting()
Dim lastRow As Long
Dim i As Integer
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 5 To lastRow
    B_value = Cells(i, "B").Value
    C_value = " '0" & CStr(B_value)
    Cells(i, "C").Value = C_value
Next i
End Sub

When you write the code in VBA, it looks like the following image.

lastRow = Cells(Rows.Count, "B").End(xlUp).Row

C_value = " '0" & CStr(B_value)

Execution of Code and Viewing Output

In our Excel sheet, we have the following data to be formatted.

After running the code, we get the following output.

How to Add Leading Zero to Number in Excel Without VBA

In the above section, we have discussed the ways you can use VBA to format a number with a leading zero. However, you may do the same thing without using VBA. There are a lot of ways to add leading zero. We will use the CONCATENATE function in the following section to do it.

📌 Steps:

• First, apply the following formula in cell C5.

• Now, we hit ENTER to get the number with the leading zero in C5.

Finally, use the AutoFill feature to fill the remaining blank cells.

How to Format Number as String in Excel VBA?

Did you ever think about whether a number in Excel is convertible to string? Maybe not, right? But it is possible. We will use a VBA Code to convert numbers into strings. The code is given below.

Sub FormatNumberToString()
Dim LastRow As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row ' Get the last row in column B
For i = 1 To LastRow
' Format the cell as a string
    Range("c" & i).NumberFormat = "@"
' Convert the number to a string
    Range("c" & i).Value = CStr(Range("B" & i).Value)
Next i
End Sub

If you write down the code in VBA Editor, it looks like the image below.

Range("c" & i).NumberFormat = "@"

Range("c" & i).Value = CStr(Range("B" & i).Value)

Execution of Code and Viewing Output

Before running the code, we see that column C has general data type like the following image.

We check the data type of column C again after running the code. At this time, it is Text type data.

How to Format Numbers with Thousand Separator in Excel VBA?

When numbers in a dataset contain larger than 4 digits, it becomes very hard to read those numbers unless there is a comma or separator in the number. In the following code, we will add a separator in the number using VBA. The code is given below(we write the code assuming that our data is in column B).

Sub AddThousandSeparator()
Dim lastRow As Long
Dim i As Integer
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 5 To lastRow
    Cells(i, "C").Value = Cells(i, "B")
    Cells(i, "C").NumberFormat = "#,##0"
Next i
End Sub

After writing the code in Editor,it looks like the image below.

Cells(i, "C").Value = Cells(i, "B")

Cells(i, "C").NumberFormat = "#,##0"

Execution of Code and Viewing Output

We run the code to see the output. Column B contains numbers without thousand separators while column C contains numbers with thousand separators.

Things to Remember

• The leading zeros in a number can be preserved by using the custom number format code “000” or by using the Text format.
• The Format function is a useful tool for formatting numbers and strings with leading zeros. It takes two arguments: the value you want to format and the custom number format code to apply.
• Be careful when converting a string with leading zeros to a number, as the Val function will remove any leading zeros and may also return unexpected results if the string contains non-numeric characters. To avoid errors, consider using the IsNumeric function to validate the input string before converting it to a number.

Download Practice Workbook

You may download the following workbook to practice yourself.

Conclusion

Hey! You have reached the end of the article on VBA format number leading zero. We have shown 4 ways including custom function. You may use any of them in your workbook based on your needs. Moreover, do let us know if you have any further queries.

Related Articles

• Excel VBA: Format Number with Comma
• Excel VBA: Number Format with No Decimal Places
• Excel VBA: Format Currency to Two Decimal Places