# Excel VBA to Format Number with Leading Zero

Get FREE Advanced Excel Exercises with Solutions!

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 number 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 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
End Function``````

When you write down the code in VBA Editor, it looks like the image below. 🔎 How Does the Code Work?

``StrLen = Len(ref)``

Len Function finds out the length of the value you want to add leading zero.

``````For i = 1 To Length
If i <= StrLen Then
Output = Output & Mid(ref, i, 1)``````
• The For loop iterates from 1 to desired length you will give input as argument.
• IF function checks whether “i” is less than or equal to “StrLen”
• When the condition is TRUE, then the code adds an extra digit to “Output” from the input number.
`````` Else
Output = "0" & Output``````

When the value of “i” becomes larger than StrLen i.e. the length of reference, it starts adding 0 in the number’s leading position until the desired length is not reached.

``LeadingZeroes = Output``

Assigns the value of variable “Output” to the output of the function.

Execution and Output

Now, we want to see how the code works. Follow the steps below and see 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. 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")
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. 🔎 How Does the Code Work?

``Set ws = Worksheets("Sheet2")``

Assign the sheet “sheet2” of the worksheet to the variable “ws”.

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

The resulting value in cell C4 will be a string that starts with the text “0” followed by the value of cell 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. Note: We have added just one 0 here. It is possible to add as many zeroes as required. Just modify the fourth line of the code.

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 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. 🔎 How Does the Code Work?

``ws.Range("C4").NumberFormat = "000000"``
• “.NumberFormat” : Property of a cell that allows to set the format in which the number in the cell will be displayed.
• The string “000000” is a custom number format code that specifies that the number in the cell should be displayed with six digits, padding with leading zeros if necessary.
``ws.Range("C4") = ws.Range("B4")``

Sets the value of cell C4 with the value of cell B4. Also, the format of the number will be the format we defined earlier.

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. Note: We have defined the number format in the code with a six-digit number which implies that if the number consists of six digits or more, the code does not work. So, before writing the code, you should know the length of the numbers in your dataset.

### 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. 🔎 How Does the Code Work?

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

This line finds the row number of the last non-empty cell in column B and then assigns that value to variable “lastRow”.

``C_value = " '0" & CStr(B_value)``

CStr(B_value) : Converts the numeric value of the variable B_value to a string.

” ‘0″  : A string that starts with a space, followed by a single quote, and a zero.

& : Concatenates the string ” ‘0″ and the string representation of B_value.

Finally, the concatenated value is assigned to the variable “C_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. Note: When you concatenate “0”, keep in mind that you must use an apostrophe symbol. Otherwise, Excel will automatically remove the leading zero and you won’t see any change in data.

## How to Add Leading Zero to Number in Excel Without VBA

In the above section, we have discussed the ways how 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.
`=CONCATENATE(0,B5)` • 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. Note: There are other ways like using the TEXT function or Ampersand operator to add leading zero. Try them in your Excel sheet to add leading zero without VBA.

## 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. 🔎 How Does the Code Work?

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

Formats a certain cell in column C as string type cell.

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

The left side of the equation converts the number into string. Then the value is assigned to the next cell in Column C.

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. In this time, it is Text type data. ## How to Format Number 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. 🔎 How Does the Code Work?

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

Read the value of a cell in column B and assign the value to the next cell which is in column C.

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

Formats the number in column C with a thousand separator.

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.

## 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 Junaed-Ar-Rahman

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed BSc in Biomedical Engineering from Bangladesh University of Engineering and Technology. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  