REPT is a Text function that repeats a string a given number of times. It is useful for filling a cell or fixed length of strings, as well as creating simple histograms. In this guide, we’re going to show you how to use REPT function and also go over some tips and error handling methods. 


Supported versions

  • All Excel versions

Excel REPT Function Syntax

REPT(text, number_times)


Arguments

text The text string you want to repeat.
number_times The number of times to repeat the text string.


Examples

Simple Use Case

=REPT(“x”,3)

The function basically repeats the string “x” 3 times, and returns “xxx”. You can obviously use any number of characters for the text to be repeated.

Fixed Length

You can use the REPT function to repeat a filler character as many times as the difference between the  number of character you want, and the length of the actual text.

For example, the following function generates a number of leading zeros (0), up to 5 digits.

=REPT(“0”,5-LEN(100))&100
Note: The REPT function converts numbers into texts. If you do not want to convert your number, apply a custom number formatting to change display without modifying the value.

Histogram

You can also create simple histograms or in-cell charts using the REPT function. All you need to do is to repeat the character as many times as the data reference. Although, you can enter whole numbers and special characters, it is a good practice to use proportional values with a special character which will work better on a visualization.

In our example, values are two-digits in length. Thus, we divided them by 10 to prevent it from repeating on a scale of 1 to 10. Additionally, we used █ and © characters instead of regular letters or symbols.

Note that the hearth (©) character is written in Symbol font. Therefore, you might have to set the font type accordingly if you want to use the following formula.

=REPT(“©”,C19/10)

Download Workbook


Summary and Tips

  • If number_times is 0 (zero), the REPT returns an empty text (“”).
  • If number_times is not an integer, it is truncated.

Issues

  • If the result of the REPT function is longer than 32,767 characters, it returns a #VALUE! error.