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.

Steps to Open VBA Editor

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

Opening the VBA Module

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.

VBA Code of Custom Function to format number with leading zero

Code Breakdown

StrLen = Len(ref)

The Len Function finds the length of the value to which you want to add a leading zero.

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

The For loop iterates from 1 to the chosen length.
IF checks whether “i” is less than or equal to “StrLen”
When the condition is TRUE, then the code adds an extra digit to “Output”.

 Else
     Output = "0" & Output

When the value of “i” is longer than StrLen , it adds 0 in the number’s leading position till the chosen length is reached.

LeadingZeroes = Output

Assigns the value of the variable “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).

Applying Function

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

Step to Give Arguments to Function

This is the output.

Output of Custom Function

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:

VBA Code of Using Ampersand to format number with leading zero

Code Breakdown

Set ws = Worksheets("Sheet2")

Assigns “sheet2” to the variable “ws”.

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

The resulting value in C4 will be a string that starts with “0” followed by the value of B4.

Running the Code and Viewing the Output

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

Cell B4 without Leading Zero

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

Output of Using Ampersand

Note: It is possible to add more than one 0.

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

This is the code:

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:

VBA Code to Add leading Zero by Number Format

Code Breakdown

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

“.NumberFormat” : allows to format the output cell.
“000000” specifies that the number in the cell should be displayed with six digits, showcasing leading zeros if necessary.

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

Sets the value of C4 to the value of B4, including the format.

Running the Code and Viewing the Output

The input is in B4.

Before Running the Code

After running the code, C4 displays the output below.

After Running the Code

Note: This code was created for a six-digit number.

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.

VBA Code to Format All number Values in Column with leading zero

Code Breakdown

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

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

C_value = " '0" & CStr(B_value)

CStr(B_value) : Converts the numeric value of 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.

The concatenated value is assigned to the variable “C_value”.

Running the Code and Viewing the Output

This is the dataset.

Data to Format

After running the code, this is the output.

Output after Formatting Data

Note: Use an apostrophe to concatenate.

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

Steps:

Use the following formula in C5.

=CONCATENATE(0,B5)

Formula of CONCATENATION

Press ENTER to see the number with the leading zero.

Result of CONCATENATION

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

Note: You can also use the TEXT function or the Ampersand operator to add a leading zero.

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.

Code to Convert Number into String

Code Breakdown

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

Formats a selected cell in column C as a string cell.

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

Converts the number into a string. The value is assigned to the next cell in Column C.

Running the Code and Viewing the Output

Column C has data in general type.

Showing the Output

After running the code Text data type will be displayed.

Output Data Type

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.

Code to Add Thousand Separator

Code Breakdown

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

Reads the value of the selected cell in column B and assigns the value to the next cell in column C.

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

Formats the number in column C with a thousand separator.

Running the Code and Viewing the Output

Column C displays numbers with thousand separators.

Output with Thousand Separator

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.

Format Number with Leading Zero.xlsm