Excel REPT Function
last modified October 5, 2026
The REPT function repeats a text string a given number of times in
Excel. It is a small text function with two arguments, and it is used for
in-cell bar charts, fixed-width padding, and repeating delimiters. This
tutorial covers the syntax and both arguments, the truncation and length
rules, practical examples, the differences from TEXT, and the
errors most often seen with REPT.
REPT Function Basics
The REPT function repeats text a given number of times. It can
create simple patterns, visual bars, or repeated strings. The result is always
a text value, even when the repeated argument is a number.
| Component | Description |
|---|---|
| Function Name | REPT |
| Syntax | =REPT(text, number_times) |
| Arguments | text - the text to repeat number_times - how many times to repeat it |
| Return Value | The repeated text string (always text) |
This table breaks down the essential components of the REPT
function. It shows the function name, basic syntax format, argument
requirements, and return value characteristics.
| Argument | Meaning |
|---|---|
| text | The text you want to repeat. It can be a literal string, a cell
reference, or an expression such as A1&"-". Numbers are
converted to text before they are repeated. |
| number_times | A zero or positive number specifying how many times text is repeated.
It can be a literal, a cell reference, or an expression such as
LEN(A1). |
REPT takes exactly two required arguments. A negative
number_times, or text that cannot be read as a number, returns
#VALUE!. A count of zero returns an empty string, a fractional
count is truncated toward zero, and the result is never longer than 32,767
characters.
Basic REPT Example
This example demonstrates the simplest use of the REPT function
with a text string and repetition count.
=REPT("X", 5)
This formula repeats the letter "X" five times. The result is "XXXXX". This
shows how REPT builds a repeated pattern from any character. The
table below lists a few common results.
| Formula | Result | Explanation |
|---|---|---|
| =REPT("ab", 3) | ababab | The two-character string is repeated 3 times |
| =REPT("-", 10) | ---------- | 10 dashes form a short divider |
| =REPT("*-", 3) | *-*-* | The two-character pattern is repeated 3 times |
| =REPT(5, 3) | 555 | Numbers are converted to text before they are repeated |
Truncating number_times
REPT does not round a fractional count; it truncates it toward
zero. A fraction below 1 vanishes completely, so a fractional count usually
produces fewer characters than expected.
| Formula | Result | Reason |
|---|---|---|
| =REPT("ab", 2.7) | abab | 2.7 is truncated to 2 |
| =REPT("ab", 2.5) | abab | Truncation, not rounding, so 2.5 also becomes 2 |
| =REPT("a", 0.4) | "" (empty string) | 0.4 is truncated to zero repeats |
| =REPT("x", 0) | "" (empty string) | A count of zero returns empty text |
| =REPT("ab", -1) | #VALUE! | The count must be zero or a positive number |
=REPT("ab", 2.7) ' abab, 2.7 is truncated to 2
=REPT("x", 0) ' "" empty text
=REPT("ab", -1) ' #VALUE!, the count is negative
When a fractional count is intended, round it first with ROUND,
or drop the fraction with INT or TRUNC, before it
reaches REPT.
The 32,767-Character Limit
A worksheet cell can hold at most 32,767 characters, and REPT
enforces that limit. If the repeated text would be longer, the formula does
not return a shortened result; it returns #VALUE!.
=LEN(REPT("a", 32767)) ' 32,767, the longest possible result
=REPT("ab", 16383) ' 32,766 characters, within the limit
=REPT("ab", 16384) ' #VALUE!, 32,768 characters is one too many
Divide 32,767 by the length of the repeated text to find the largest safe
count, and recalculate whenever the text can change:
=INT(32767 / LEN(A1)).
Creating In-Cell Bar Charts
REPT is commonly used to create simple in-cell bar charts. This
example shows how to visualize data using repeated characters.
| Value in column A | Formula in column B | Result |
|---|---|---|
| 5 | =REPT("|", A1) | ||||| |
| 8 | =REPT("|", A2) | |||||||| |
| 3 | =REPT("|", A3) | ||| |
The table shows values in column A and the corresponding bar charts in column B. Each bar consists of repeated "|" characters, and its length matches the value in column A: 5, 8, and 3 characters respectively.
=REPT("|", A1)
This formula creates a bar of "|" characters whose length matches the value in A1. If A1 contains 5, the result is "|||||". This technique provides quick data visualization without charts.
Padding Text to Fixed Length
REPT can pad text to a fixed length by repeating spaces or other
characters. This example demonstrates text padding.
| Value | Formula | Spaces added | Result length |
|---|---|---|---|
| Apple | =A1 & REPT(" ", 10-LEN(A1)) | 5 | 10 |
| Banana | =A2 & REPT(" ", 10-LEN(A2)) | 4 | 10 |
This table shows how to pad text entries to a fixed length of 10 characters.
The formula calculates the needed spaces from each text's length with
LEN.
=A1 & REPT(" ", 10-LEN(A1))
This formula pads the text in A1 with spaces to reach 10 characters total. For "Apple" (5 characters) it adds 5 spaces, and for "Banana" (6 characters) it adds 4, so both results are exactly 10 characters long. This ensures consistent column widths for better data presentation.
The subtraction is also a trap. If the text is already 10 characters or
longer, 10-LEN(A1) is zero or negative, and REPT
returns an empty string or #VALUE!. Guard the count with
MAX.
=A1 & REPT(" ", MAX(0, 10-LEN(A1)))
Creating Custom Borders
REPT can generate custom borders or separators by repeating border
characters. This example creates a decorative divider.
=REPT("-*", 20)
This formula creates a 40-character border by repeating "-*" 20 times. The result is "-*-*-*-*...". This technique is useful for section dividers in reports or separating content visually.
Generating Number Patterns
REPT can create number patterns when combined with other functions.
This example shows a pyramid pattern.
| Cell | Formula | Result |
|---|---|---|
| A1 | =REPT(ROW(), ROW()) | 1 |
| A2 | =REPT(ROW(), ROW()) | 22 |
| A3 | =REPT(ROW(), ROW()) | 333 |
The table demonstrates how to create a number pyramid where each row number is repeated according to its row position. Row 1 shows "1", row 2 shows "22", and row 3 shows "333".
=REPT(ROW(), ROW())
ROW() returns the number of the row the formula sits in, so the
formula uses no other cell. In row 3 it repeats "3" three times, creating
"333". Because REPT converts the number to text first, the result
of this pattern is text, not a number. This shows REPT's creative potential
with numeric patterns.
Creating Filled Progress Bars
REPT can create filled progress bars by repeating block characters
based on percentage values. This example demonstrates percentage
visualization.
| Value in column A | Formula in column B | Result |
|---|---|---|
| 0.75 | =REPT("█", ROUND(A1*10,0)) & REPT("░", 10-ROUND(A1*10,0)) | 8 filled blocks and 2 empty blocks |
| 0.5 | =REPT("█", ROUND(A2*10,0)) & REPT("░", 10-ROUND(A2*10,0)) | 5 filled blocks and 5 empty blocks |
The table shows percentage values in column A and the corresponding 10-character progress bars in column B. Filled blocks (█) represent completion, and empty blocks (░) represent the work that remains.
=REPT("█", ROUND(A1*10,0)) & REPT("░", 10-ROUND(A1*10,0))
This formula creates a 10-character progress bar where the filled blocks represent the percentage in A1. For 0.75 (75%) it shows 8 filled blocks and 2 empty blocks, and for 0.5 (50%) it shows 5 of each. This provides instant visual feedback on progress.
The ROUND calls are not decoration. Without them,
REPT truncates both counts, so 7.5 becomes 7 filled blocks and
2.5 becomes 2 empty blocks, and the bar shrinks to 9 characters. Rounding
first keeps every bar exactly 10 characters long.
REPT vs TEXT
Both functions return text, but they solve different problems. REPT
repeats one string an exact number of times, while TEXT renders a
single value with a number format.
| Formula | Result | Use it when |
|---|---|---|
| =REPT("0", 4-LEN(42)) & 42 | 0042 | You pad with one repeated character and count the length yourself |
| =TEXT(42, "0000") | 0042 | The padding should follow a number format |
| =REPT("ab", 2) | abab | You repeat an arbitrary string, not a number |
| =TEXT(1234.5, "#,##0.00") | 1,234.50 | You need separators or decimals; REPT cannot produce them |
TEXT knows the value it is formatting, so the pattern adapts:
=TEXT(42, "0000") returns 0042 and
=TEXT(4321, "0000") returns 4321, with no arithmetic
at all. A REPT pad must compute the difference with
LEN as in =REPT("0", 4-LEN(A1)) & A1. Reach for
REPT when the repeated text is the point, and for
TEXT when a formatted number is the point. Both results are text,
so convert them with -- or VALUE before using them in
calculations.
REPT Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| number_times is negative | #VALUE! error | Use MAX(0, count) or correct the source value |
| Result would exceed 32,767 characters | #VALUE! error | Shorten the text or lower the count; the safe count is INT(32767/LEN(text)) |
| number_times is not an integer | Fewer repeats than expected | Round the count with ROUND, INT, or TRUNC before calling REPT |
| number_times is 0 or the count cell is empty | The cell looks empty | Expected: zero returns an empty string, and an empty cell counts as zero |
| number_times is text that is not a number | #VALUE! error | Supply a number, or convert the value with VALUE |
| Padding target is shorter than the text | #VALUE! error from a negative count | Guard the count with MAX(0, target-LEN(A1)) |
| Referenced cell contains an error | The same error is returned | REPT cannot mask errors; handle them with IFERROR |
The REPT function is one of Excel's simplest text tools, and its
rules fit on an index card: repeat an exact string, truncate any fractional
count toward zero, treat zero as an empty string, never go negative, and never
exceed 32,767 characters. Because the result is always text,
REPT pairs naturally with LEN for padding, with
ROW for in-cell bar charts, and with TEXT when a
number format rather than a repeated character is what you need. Keep the
truncation and length limits in mind and REPT will draw charts,
build patterns, and align text reliably, with no formatting required.
Author
List all Excel Formulas.