Using Excel VBA to Format a Number with a Leading Zero – 4 Methods

How to Launch the VBA Editor in Excel

Enable the Developer tab on your ribbon.

• Go to the Developer tab.
• Select Visual Basic.

• Select Insert.
• Choose Module.
• A new Module will be created.

Method 1- Adding Leading Zeros by Creating a Custom Function

Create a function in VBA ( “LeadingZeroes”). It has 2 arguments:  the cell range of cells with numbers to format and number of zeros you want in the result.

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

Enter the code in the VBA Editor.

StrLen = Len(ref)

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

 Else
     Output = "0" & Output

LeadingZeroes = Output

Running the Code and Viewing the Output

Enter an equal sign in the cell where you want to see the output.

Select the name of the function (LeadingZeroes).

Select the input and expected length of the output. Here, B4 with “5” as length

This is the output.

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

Method 2 – Adding Leading Zeros using the Ampersand Operator

Use the Ampersand and declare the target sheet.

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

Enter the code:

Set ws = Worksheets("Sheet2")

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

Running the Code and Viewing the Output

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

After running the code, C4 displays a number with a leading zero.

Method 3 – Using the Number Format to Add a Leading Zero

It adds 0 based on the size of the number. The code is:

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

Enter the code:

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

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

Running the Code and Viewing the Output

The input is in B4.

After running the code, C4  displays the output below.

Method 4 – Format all Numbers in a Column using a Leading Zero

This is the code.

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

Enter the code.

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

C_value = " '0" & CStr(B_value)

Running the Code and Viewing the Output

This is the dataset.

After running the code, this is the output.

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

Use the CONCATENATE function.

Steps:

• Use the following formula in C5.

• Press ENTER to see the number with the leading zero.

Drag down the Fill Handle to see the result in the rest of the cells.

How to Format a Number as String in Excel VBA?

This is the code.

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

Enter the code.

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

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

Running the Code and Viewing the Output

Column C has general data type.

After running the code Text data type will be displayed.

How to Format Numbers with a Thousand Separator in Excel VBA?

This is the code.

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

Enter the code.

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

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

Running the Code and Viewing the Output

Column C displays 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 use the Text format.
• The Format function takes two arguments: the value you want to format and the custom number format code to apply.
• When converting a string with leading zeros to a number, the Val function will remove any leading zeros. To avoid errors, consider using the IsNumeric function to validate the input string.

Download Practice Workbook

Download the following workbook to practice.

Related Articles

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