Repeats text a specified number of times. Essential for creating visual bars, formatting text, and generating repeated patterns in Excel.
Master the fundamentals of Excel REPT function
The REPT function repeats a text string a specified number of times, creating concatenated repetitions. It's essential for creating visual bar charts, formatting displays, generating repeated patterns, and building simple text-based graphics in Excel.
Repeats text string multiple times
Creates visual bars with characters
Number of times can be calculated
Returns empty string when number_times is 0
Function-specific parameters
Function-specific return type
Create simple bar charts with characters
Format text with repeated separators
Create star rating displays
Show progress with repeated characters
Exact matching required
Returns numeric position
Handles missing text gracefully
=REPT(text, number_times)The text string to repeat
The number of times to repeat the text (must be >= 0)
Text string repeated the specified number of times
Description: Repeats text a specified number of times. Useful for creating visual bars and patterns.
Repeat text multiple times
Repeats 'Hello' three times consecutively
Simple VBA implementation of REPT function
' Basic REPT in VBA
Range("B1").Value = Application.WorksheetFunction.Rept("Hello", 3)
' Returns: "HelloHelloHello"
' Using VBA Rept function
Dim text As String
text = "-"
Dim repeatCount As Integer
repeatCount = 10
Range("B2").Value = Application.WorksheetFunction.Rept(text, repeatCount)
' Returns: "----------"
' Create visual bars for range
Sub CreateVisualBars()
Dim cell As Range
For Each cell In Range("A1:A10")
If IsNumeric(cell.Value) Then
Dim barLength As Integer
barLength = Int(cell.Value)
cell.Offset(0, 1).Value = Application.WorksheetFunction.Rept("|", barLength)
End If
Next cell
End Sub
' Create star rating
Sub CreateStarRating()
Dim rating As Integer
rating = 4
Range("B1").Value = Application.WorksheetFunction.Rept("*", rating)
' Returns: "****"
End SubCreate dynamic visual charts and progress bars
' Create progress bar
Function CreateProgressBar(value As Double, maxValue As Double, barLength As Integer) As String
Dim filledLength As Integer
filledLength = Int((value / maxValue) * barLength)
Dim emptyLength As Integer
emptyLength = barLength - filledLength
CreateProgressBar = Application.WorksheetFunction.Rept("█", filledLength) & _
Application.WorksheetFunction.Rept("░", emptyLength)
End Function
' Create horizontal bar chart
Sub CreateBarChart()
Dim cell As Range
Dim maxValue As Double
maxValue = Application.WorksheetFunction.Max(Range("A1:A10"))
For Each cell In Range("A1:A10")
If IsNumeric(cell.Value) Then
Dim barLength As Integer
barLength = Int((cell.Value / maxValue) * 20)
cell.Offset(0, 1).Value = Application.WorksheetFunction.Rept("=", barLength)
End If
Next cell
End Sub
' Format with separator
Sub FormatWithSeparator()
Dim values() As Variant
values = Array("A", "B", "C")
Dim result As String
Dim i As Integer
result = values(0)
For i = 1 To UBound(values)
result = result & Application.WorksheetFunction.Rept("-", 3) & values(i)
Next i
Range("A1").Value = result
' Returns: "A---B---C"
End SubCreate simple bar charts with characters
Create star rating displays
Show progress with repeated characters
Create separators in text
REPT with negative number_times returns #VALUE! error
=REPT(A1, MAX(0, B1))Solution: Ensure number_times is >= 0. Use MAX to prevent negatives: =REPT(text, MAX(0, number_times))
REPT truncates decimal number_times
=REPT(A1, INT(B1))Solution: This is expected. REPT uses integer part only. REPT("A", 2.7) = REPT("A", 2)
REPT may be slow or cause issues with very large repetition counts
=REPT(A1, MIN(B1, 100))Solution: Limit repetition count to reasonable values (typically < 32767). Consider alternative approaches for very large counts.
REPT with empty text returns empty string regardless of count
=REPT("", 10) returns ""Solution: This is expected. Ensure text parameter contains at least one character.