Add trailing zero to a number

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

Excel

Add trailing zero to a number using the & sign

Excel

Add trailing zero to a number using the CONCATENATE function

VBA

Add trailing zero to a number using & sign

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

EXCEL

Hard coded formula

Cell reference formula

=B5&"0"

=B5&C5

GENERIC FORMULA

=number&0

ARGUMENTS
number: The number at the end of which you want to insert a zero.

GENERIC FORMULA

=number&zero

ARGUMENTS
number: The number at the end 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 trailing 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 at the end of a number captured in cell B5.

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

EXCEL

Hard coded formula

Cell reference formula

=CONCATENATE(B5,0)

=CONCATENATE(B5,C5)

GENERIC FORMULA

=CONCATENATE(number,0)

ARGUMENTS
number: The number at the end of which you want to insert a zero.

GENERIC FORMULA

=CONCATENATE(number,zero)

ARGUMENTS
number: The number at the end 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 trailing 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 at the end of a number captured in cell B5 with the use of a CONCATENATE function.

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

VBA

Hard coded against single cell

Sub Add_trailing_zero_to_number()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

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

End Sub

Cell reference against single cell

Sub Add_trailing_zero_to_number()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

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

End Sub

Hard coded against range of cells

Sub Add_trailing_zero_to_number()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'add a trailing zero to a number

For x = 5 To 8

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

End Sub

Cell reference against range of cells

Sub Add_trailing_zero_to_number()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'add a trailing zero to a number

For x = 5 To 8

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

End Sub

Related Topic	Description	Related Topic and Description
Add leading zero to a number	How add a leading zero (0) to a number through the use of an Excel formula or VBA
Remove trailing spaces in a cell	How remove only the trailing spaces from text in a cell through the use of an Excel formula or VBA
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