Add trailing zeros to a number to make number certain length

This tutorial shows how to add trailing zeros (0) to a number to make a number certain length through the use of an Excel formula, with the & sign, REPT and LEN functions or VBA

Excel

Add trailing zeros to a number to make number certain length using the & sign, REPT and LEN functions

VBA

Add trailing zeros to a number to make number certain length using VBA

EXCEL FORMULA 1. Add trailing zeros to a number to make number certain length using the & sign, REPT and LEN functions

EXCEL

Hard coded formula

Add trailing zeros to a number to make number certain length

Cell reference formula

=B5&REPT("0",5-LEN(B5))

=B5&REPT(D5,C5-LEN(B5))

GENERIC FORMULA

=number&REPT("0",length-LEN(number))

ARGUMENTS
number: The number that you want to add trailing zeros to make it a specific length.
length: A number that represents the length that you want to make a selected number.

GENERIC FORMULA

=number&REPT(zero,length-LEN(number))

ARGUMENTS
number: The number that you want to add trailing zeros to make it a specific length.
length: A number that represents the length that you want to make a selected number.
zero: A cell that captures the number zero.

EXPLANATION

This formula uses the & sign, REPT and LEN functions to add trailing zeros to a number to make the number a certain length.

Click on either the Hard Coded or Cell Reference button to view the formula that either has the number zero (0) and the number that represents the length that you want to convert a selected number to entered directly in the formula or referenced to a cell.

In this example we are adding two trailing zeros to make the number in cell B5 be the length of five.

VBA CODE 1. Add trailing zeros to a number to make number certain length

VBA

Hard coded against single cell

Sub Add_trailing_zeros_to_number_to_make_certain_length()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'add trailing zeros to a number to make number certain length
ws.Range("C5") = ws.Range("B5") & WorksheetFunction.Rept("0", 5 - Len(ws.Range("B5")))

End Sub

Cell reference against single cell

Sub Add_trailing_zeros_to_number_to_make_certain_length()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'add trailing zeros to a number to make number certain length
ws.Range("E5") = ws.Range("B5") & WorksheetFunction.Rept(ws.Range("D5"), ws.Range("C5") - Len(ws.Range("B5")))

End Sub

Hard coded against range of cells

Sub Add_trailing_zeros_to_number_to_make_certain_length()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'add trailing zeros to a number to make number certain length

For x = 5 To 8

ws.Range("C" & x) = ws.Range("B" & x) & WorksheetFunction.Rept("0", 5 - Len(ws.Range("B" & x)))
Next x

End Sub

Cell reference against range of cells

Sub Add_trailing_zeros_to_number_to_make_certain_length()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'add trailing zeros to a number to make number certain length

For x = 5 To 8

ws.Range("E" & x) = ws.Range("B" & x) & WorksheetFunction.Rept(ws.Range("D5"), ws.Range("C" & x) - Len(ws.Range("B" & x)))
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
Add trailing zero to a number	How to add trailing zero 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