Text function
Excel REPT function
REPT repeats a piece of text a number of times. =REPT("-",10) gives ten dashes. It is the quick way to draw a bar chart inside a cell, show a star rating, or pad a code with leading zeros.
=REPT(text, number_times)Arguments
text- The text to repeat, in quotes or from a cell.
number_times- How many times to repeat it. Decimals are cut off, 0 gives empty text, and a negative number returns #VALUE!.
REPT examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Rating | Units sold | Item no |
| 2 | Desk lamp | 4 | 120 | 7 |
| 3 | Monitor arm | 5 | 85 | 42 |
| 4 | Cable tray | 3 | 40 | 315 |
| 5 | Footrest | 2 | 15 | 1208 |
Download this sheet as an Excel practice file (.xlsx)
Result: ★★★★
Four stars for a rating of 4.
Result: ★★★☆☆
Filled stars for the rating and empty ones for the rest of 5, so a 3 reads as three of five.
Result: ||||||||
An in-cell bar chart, one bar per 10 units. 85 units is 8.5, and REPT cuts the decimal, so it draws 8 bars.
Result: 000042
Pads the item number to six digits: 000042. TEXT(D3,"000000") gives the same result.
How to use REPT
- Click the cell for the result.
- Type =REPT( and the text to repeat in quotes, such as "|".
- Add a comma and the number of times, or a cell or formula that works it out.
- Press Enter and fill down.
Common REPT mistakes
Getting #VALUE!
number_times is negative, for example 5-B2 when a rating is above 5. Guard it with MAX: =REPT("☆",MAX(0,5-B2)). A result longer than 32,767 characters is also #VALUE!.
Bars that run off the screen
REPT draws one character per unit, so 1,200 units is 1,200 characters. Scale it, for example =REPT("|",ROUND(C2/MAX($C$2:$C$5)*20,0)) for bars at most 20 wide.
Bars that look uneven
In most fonts characters have different widths. Use one character for every bar, or set the column to a fixed-width font such as Consolas.
REPT FAQ
How do I make an in-cell bar chart in Excel?
Use =REPT("|",B2/10) to draw one bar per 10 units. For a built-in option, select the cells and choose Home > Conditional Formatting > Data Bars.
How do I add leading zeros in Excel?
=TEXT(A2,"000000") pads a number to six digits. =REPT("0",6-LEN(A2))&A2 does the same and also works on codes stored as text.
How do I show star ratings in Excel?
Use =REPT("★",B2)&REPT("☆",5-B2). It shows filled stars for the rating and empty ones up to 5.
Related functions
Updated