Repeat a value n times in a cell

This tutorial shows how to repeat a value n number of times in a cell through the use of an Excel formula, with the REPT function or VBA

EXCEL FORMULA 1. Repeat a value n times in a cell using the REPT function

EXCEL

Hard CodedCell Reference

Hard coded formula

Cell reference formula

=REPT("a",5) =REPT(B5,C5)

GENERIC FORMULA =REPT(value,num_rept) ARGUMENTS value: A value that you want to repeat. num_rept: Number of times you want to repeat the value. GENERIC FORMULA =REPT(value,num_rept) ARGUMENTS value: A value that you want to repeat. num_rept: Number of times you want to repeat the value. EXPLANATION This formula uses the REPT function to repeat a value specific number of times. Click on either the Hard Coded or Cell Reference button to view the formula that either has the value and number of specific times it should be repeated entered directly in the formula or referenced to cells that capture these values. In this example we are repeating value "a" five times.

VBA CODE 1. Repeat a value n times in a cell using VBA

VBA

Hard CodedCell Reference

Single CellLoop Cells

Hard coded against single cell

Sub Repeat_value_n_times()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'repeat a specific n number of times
ws.Range("B5") = WorksheetFunction.Rept("a", 5)

End Sub

Cell reference against single cell

Sub Repeat_value_n_times()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'repeat a specific n number of times
ws.Range("D5") = WorksheetFunction.Rept(ws.Range("B5"), ws.Range("C5"))

End Sub

Hard coded against range of cells

Sub Repeat_value_n_times()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'repeat a specific n number of times

For x = 5 To 8

ws.Range("B" & x) = WorksheetFunction.Rept("a", 5)
Next x

End Sub

Cell reference against range of cells

Sub Repeat_value_n_times()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'repeat a specific n number of times

For x = 5 To 8

ws.Range("D" & x) = WorksheetFunction.Rept(ws.Range("B" & x), ws.Range("C" & x))
Next x

End Sub

RELATED TOPICS

Related Topic	Description	Related Topic and Description
Return substring	How to extract a substring with a nominated start and end position through the use of an Excel formula or VBA