Excel contains a special function for repeating text named REPT which stands for “repeat.” The REPT 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 the number “3,” we’ll get a triple A. If I expose these arguments on the worksheet, I can quickly add a few more examples. In each case, Excel simply repeats the text you provide as many times as you specify. So, what can you use the REPT function for? Well, one thing you can do with REPT is build a simple histogram. For example, here we have one-day 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 a much nicer job. But if you want a dead-simple, in-cell chart, the REPT function is an easy option. You can also use the REPT function to pad numbers and 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 six characters long. I’ll start off with a simple formula that repeats the zero six times. Now we have the zeros and, using concatenation, I can join the zeros to the text. That’s closer to what we want, but we don’t want six zeros plus the text. We only want as many zeros as needed so that the final result is always six characters long. The solution is to calculate the length of the input text, and then subtract that number from six; 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 this formula down, all results are exactly six characters long. Although this example is padding text with zeros, I can use any other character I like. For example, I could use an asterisk or a hyphen. You can use this same approach to create part numbers or any kind of code that needs to be a standard length.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
