How to Use the REPT Function in Google Sheets
=REPT(text_to_repeat, number_of_repetitions). =REPT("ha", 4) returns hahahaha.
What REPT does
REPT repeats a character or string as many times as you ask. It is good for padding, separators and, in a neat trick, for turning a number into a small bar so a column of figures becomes a quick visual.
REPT syntax
=REPT(text_to_repeat, number_of_repetitions)
Per Google's help page, text_to_repeat is the character or string to repeat, and number_of_repetitions is how many times it appears. The result cannot exceed the 32,000-character cell limit, otherwise REPT returns a #VALUE! error. REPT does not insert spaces between repetitions. To include them, add a space to the end of the text to repeat. The result then has at least one trailing space, which you can remove with TRIM.
A step-by-step example
=REPT("ha", 4)
This returns hahahaha. It matches the sample on the help page.
Build a bar from a number
Cell A2 holds the number 5.
| A | |
|---|---|
| 1 | Score |
| 2 | 5 |
=REPT("|", A2)
This returns |||||, five bars. Fill the formula down beside a column of numbers and longer bars mean bigger numbers. For a proper chart in a single cell, SPARKLINE exists, but REPT needs nothing more than a formula.
Common mistakes
- Expecting spaces between copies. Add one to the text to repeat.
- A huge repetition count. Beyond the cell limit you get #VALUE!.
- Swapping the arguments. The text comes first, the count second.
Try it yourself
Use the number above (A2). Type your formula into the box, starting with =, then press Check.
Exercise 1: Repeat ha
Write a formula that repeats "ha" four times.
Exercise 2: A bar from a number
Write a formula that repeats the character | as many times as the number in A2.
What to learn next
- CLEAN: remove non-printable characters.
- TRIM: remove extra spaces from text.
- LEN: count the characters in a cell.
Syntax and rules in this guide were checked against Google's REPT help page. See all guides.