Quick, clean, and to the point

How to use the REPT function to repeat things

In this video, we'll look at how to use the REPT function in Excel to repeat text.

Excel contains a special function for repeating text named REPT, which which stands for repeat.

The repeat function takes two arguments, the text to repeat, and the number of times to repeat the text. So, if I enter an upper case "A" and and 3, we'll get a triple A.

If I expose these arguments on the worksheet, I can quickly add a few more examples. ..A triple A again. 10 asterisks. and 12 hash characters.

In each case, Excel simply repeats the text you provide as many times as you specify.

So, what can you use the repeat function for?

Well one thing you can do with repeat is visualize data with a simple histogram.

For example, here we have one-days sales for milkshakes, broken down by flavor. To display this information visually, I can use REPT with any text I like, and just plug in the sales number.

To be sure, Excel has great charting tools, and even a special kind of conditional formatting that can do this too. But if you want a dead-simple in-cell chart, REPT is an easy option.

The REPT function can also be used to pad numbers or other text.

For example, suppose I have some numbers or text that I want to pad with zeros, so that the final result is always 6 characters long.

I'll start off with a simple formula that repeats the zero 6 times.

Now we have the zeros, and using concatenation, I can join the zeros to the text.

That's closer, but we don't want want 6 zeros plus the text, we want we only want as many zeros as needed so that the final result is always 6 characters.

The solution is to calculate the length of the input text and then subtract that number from 6, then use the result as the number of times to repeat the zeros.

I can use the LEN function to calculate the length of the text, and subtract that from the number we're using for repetitions.

When I copy the formula down, all results are exactly 6 characters long.

Although this example is padding text with zeros, I can use any other character. For example I could use an asterisk.

You can use this same idea to create part numbers, or any kind code that needs to be a standard length.


Related shortcuts

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.