Add leading zero to a number

This tutorial shows how to add a leading zero (0) to a number through the use of an Excel formula, with the & sign, CONCATENATE and TEXT functions or VBA

Excel

Add leading zero to a number using the & sign

Excel

Add leading zero to a number using the CONCATENATE function

Excel

Add leading zero to a number using the TEXT function

VBA

Add leading zero to a number using & sign

VBA

Add leading zero to a number using NumberFormat

EXCEL FORMULA 1. Add leading zero to a number using the & sign

EXCEL

Hard coded formula

Cell reference formula

="0"&B5

=C5&B5

GENERIC FORMULA

=0&number

ARGUMENTS
number: The number in front of which you want to insert a zero.

GENERIC FORMULA

=zero&number

ARGUMENTS
number: The number in front of which you want to insert a zero.
zero: A cell that captures the number zero.

EXPLANATION

This formula uses the & sign to insert a leading zero to a number.

Click on either the Hard Coded or Cell Reference button to view the formula that either has the number zero (0) entered directly in the formula or referenced to a cell.

In this example we are adding a zero in front of a number captured in cell B5.

EXCEL FORMULA 2. Add leading zero to a number using the CONCATENATE function

EXCEL

Hard coded formula

Cell reference formula

=CONCATENATE(0,B5)

=CONCATENATE(C5,B5)

GENERIC FORMULA

=CONCATENATE(0,number)

ARGUMENTS
number: The number in front of which you want to insert a zero.

GENERIC FORMULA

=CONCATENATE(zero,number)

ARGUMENTS
number: The number in front of which you want to insert a zero.
zero: A cell that captures the number zero.

EXPLANATION

This formula uses the CONCATENATE function to insert a leading zero to a number

Click on either the Hard Coded or Cell Reference button to view the formula that either has the number zero (0) entered directly in the formula or referenced to a cell.

In this example we are concatenating a zero in front of a number captured in cell B5 with the use of a CONCATENATE function.

EXCEL FORMULA 3. Add leading zero to a number using the TEXT function

EXCEL

Hard coded formula

Cell reference formula

=TEXT(B5,"000000")

=TEXT(B5,C5)

GENERIC FORMULA

=TEXT(number,"000000")

ARGUMENTS
number: The number in front of which you want to insert a zero.

GENERIC FORMULA

=TEXT(number,zero)

ARGUMENTS
number: The number in front of which you want to insert a zero.
zero: A cell that captures the zeros, you will need to insert an apostrophe ' sign in front of the zeros in the cell that captures the zeros.

EXPLANATION

This formula uses the TEXT function to insert a leading zero to a number. It will add leading zeros to ensure the number of of a certain length, which in this example is a length of size numbers. Given the number in cell B5 has five numbers this formula will add a single value of zero (0) to the front of the number. If the number in cell B5 only comprised four numbers, this formula would add two zeros to the front of the number.

Click on either the Hard Coded or Cell Reference button to view the formula that either has the zeros entered directly in the formula or referenced to a cell.

VBA CODE 1. Add leading zero to a number with the & sign

VBA

Hard coded against single cell

Sub Add_leading_zero_to_number()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'add a leading zero to a number
ws.Range("C5") = "'0" & ws.Range("B5")

End Sub

Cell reference against single cell

Sub Add_leading_zero_to_number()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'add a leading zero to a number
ws.Range("D5") = "'" & ws.Range("C5") & ws.Range("B5")

End Sub

Hard coded against range of cells

Sub Add_leading_zero_to_number()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'add a leading zero to a number

For x = 5 To 8

ws.Range("C" & x) = "'0" & ws.Range("B" & x)
Next x

End Sub

Cell reference against range of cells

Sub Add_leading_zero_to_number()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'add a leading zero to a number

For x = 5 To 8

ws.Range("D" & x) = "'" & ws.Range("C5") & ws.Range("B" & x)
Next x

End Sub

VBA CODE 2. Add leading zero to a number using the NumberFormat function

VBA

Hard coded against single cell

Sub Add_leading_zero_to_number()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'add a leading zero to a number if the number comprises five numbers as we are converting the cell format to "000000"
ws.Range("C5").NumberFormat = "000000"
ws.Range("C5") = ws.Range("B5")

End Sub

Cell reference against single cell

Sub Add_leading_zero_to_number()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'add a leading zero to a number if the number comprises five numbers as we are converting the cell format to "000000"
ws.Range("D5").NumberFormat = ws.Range("C5")
ws.Range("D5") = ws.Range("B5")

End Sub

Hard coded against range of cells

Sub Add_leading_zero_to_number()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'add a leading zero to a number if the number comprises five numbers as we are converting the cell format to "000000"
For x = 5 To 8

ws.Rangews.Range("C" & x).NumberFormat = "000000"
ws.Range("C" & x) = ws.Range("B" & x)

Next x

End Sub

Cell reference against range of cells

Sub Add_leading_zero_to_number()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'add a leading zero to a number if the number comprises five numbers as we are converting the cell format to "000000"
For x = 5 To 8

ws.Range("D" & x).NumberFormat = ws.Range("C5")
ws.Range("D" & x) = ws.Range("B" & x)

Next x

End Sub

Related Topic	Description	Related Topic and Description
Remove leading zeros	How to remove the leading zeros in a string through the use of an Excel formula or VBA
Remove leading spaces in a cell	How to remove only the leading spaces from text in a cell through the use of an Excel formula or VBA