Exceljet

Quick, clean, and to the point

Excel REPT Function

Excel REPT function
Summary 

The Excel REPT function repeats characters a given number of times. For example, =REPT("x",5) returns "xxxxx".

Purpose 
Repeat text as specified
Return value 
The repeated text.
Syntax 
=REPT (text, number_times)
Arguments 
  • text - The text to repeat.
  • number_times - The number of times to repeat text.
Version 
Usage notes 

The REPT function repeats characters a specified number of times. Although REPT can repeat numbers as well as text, the result from REPT is always a text value. REPT takes two arguments, text and number_times. Text is the character(s) to repeat, and number_times is the number of times text should be repeated. 

REPT can be used to pad values to a certain length or to build a simple in-cell histogram. To pad numeric values with zeros, a custom number format may be a better option.

Examples

To repeat "x" five times, you can use the following formula:

=REPT("x",5) // returns "xxxxx"

Inputs to REPT can be variable. In the example shown above, REPT is configured to repeat the values in column B using the count in column C.  The formula in D5 is:

=REPT(B5,C5) // returns "-----"

REPT can be combined with other functions to calculate a variable count. For example, to pad a text string with a specific character so that all strings are the same length, you can use REPT together with the LEN function like this:

=A1&REPT("*",count-LEN(A1))

In this formula, "count" is the desired final length in characters. Detailed explanation here.

The REPT function appears in more advanced formulas that solve some tricky problems. See below for more examples.

Notes

  • REPT can repeat numbers but the result is text.
  • Number_times should be zero or a positive integer, otherwise, REPT will return #VALUE!
  • If number_times is zero, REPT returns an empty string ("").

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.