Excel REPT Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 72 of 105 · Full Excel course

The Excel REPT function repeats a text string a given number of times and returns the result as one text value. It is used for padding values to a fixed width, drawing simple in-cell bar charts and rating stars, and generating separator lines. REPT works in every version of Excel and the output can be up to 32,767 characters long.

Table of Contents

Syntax

=REPT(text, number_times)

Arguments

Argument	Required / Optional	Meaning
text	Required	The text to repeat. It can be one character, a word, a cell reference or a symbol returned by CHAR or UNICHAR.
number_times	Required	A positive number of repetitions. Decimals are truncated, so 3.9 repeats three times. Zero returns an empty string; a negative number returns #VALUE!.

How REPT works

REPT builds the output by concatenating the text with itself number_times times. Because number_times can be a formula, the length of the output can be driven by data, which is what makes REPT useful for visual indicators. The result is text even when the repeated string is a digit, so =REPT("7",3) returns the text “777”. If the final string would exceed 32,767 characters, the function returns #VALUE!.

Step-by-step example: in-cell bar chart

Enter sales figures in B2:B10, for example 45, 120, 80.
In C2 enter =REPT("|",B2/10). For 45 this returns four bars because 4.5 is truncated to 4.
Select C2:C10 and change the font to Playbill (Windows) so the bars join into a solid block. Alternatively use =REPT(UNICHAR(9608),B2/10), which draws a solid block character in any font.
Fill the formula down. The column now shows a bar proportional to each value, with no chart object required.

Practical use cases

1. Pad numbers with leading zeros

=REPT("0",6-LEN(A2))&A2

Turns 42 into 000042. Because the result is text, the zeros are preserved when exported.

2. Star rating

=REPT(UNICHAR(9733),A2)&REPT(UNICHAR(9734),5-A2)

Shows filled stars for the score and empty stars for the remainder.

3. Right-align text in a fixed-width export

=REPT(" ",12-LEN(A2))&A2

4. Get the last word of a sentence

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

Each space is expanded to 100 spaces, so the last 100 characters can contain only the final word and padding, which TRIM removes.

5. Separator line under a heading

=REPT("-",LEN(A1))

Common mistakes and tips

#VALUE! on negative counts: the padding formulas above fail when the text is already longer than the target width. Wrap the count in MAX(0, …).
Result is text: zero-padded numbers cannot be summed. Keep a numeric column and use REPT only for display or export.
Cell width: long REPT bars spill into neighbouring empty cells or are cut off. Set a fixed column width and scale the count.
Proportional fonts: characters have different widths, so bars built from letters look uneven. Use |, block characters or a monospaced font.
Performance: very long repeated strings in thousands of rows slow down recalculation; keep the multiplier sensible.

REPT compared with conditional formatting data bars

Since Excel 2007, conditional formatting data bars produce the same visual effect as a REPT bar chart, scale automatically and stay numeric. REPT still has advantages in specific situations: it works in a text column that will be exported or pasted into an email, it can combine two symbols in one cell (filled and empty stars), it can be driven by any formula rather than the cell’s own value, and it prints consistently in every Excel version and in Google Sheets. For dashboards that stay inside Excel, data bars are usually the cleaner choice; for text-based reports, exports and simple visual flags, REPT remains quick and dependable.