# 30 Excel Functions in 30 Days: 15 – REPT

Yesterday, in the 30XL30D challenge, we took things easy, with the T function. It checks a value to see if it's text, and other functions do that too.
For day 15 in the challenge, we'll examine the REPT function, which repeats a text string, a specified number of times. It's another Text function, and has a few interesting uses.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file -- the 30 Excel Functions in 30 Days eBook Kit (\$10).
So, let's take a look at the REPT information and examples, and if you have other tips or examples, please share them in the comments.

### Function 15: REPT

The REPT function repeats a text string, a specified number of times. The REPT function repeats a text string, a specified number of times. The REPT function repeats a text string, a specified number of times.
Note: Creating hilarious effects like the above paragraph would be much easier with the REPT function. 😉

### How Could You Use REPT?

The REPT function can be used to fill a cell with a character, or in innovative ways, such as:

• Creating an in-cell bar or dot chart
• Keeping a quick tally
• Finding the last text entry in a column

### REPT Syntax

The REPT function has the following syntax:

• REPT(text,number_times)
• text is the item that you want to repeat.
• number_times is a positive number

### REPT Traps

• The limit to the text string is 32,767 characters -- anything higher, and the formula will result in an error.
• If number_times is a decimal, it will be truncated to an integer.
• If number_times is zero, the result is an empty string.

### REPT Alternative

If you simply want to fill a cell with a character, you can use cell formatting instead:

1. In a cell, type the character(s) that you want as the fill, e.g. a hyphen or period
2. With the cell selected, press Ctrl + 1, to open the Format Cells window
3. Click the Alignment tab, and from the Horizontal setting, select Fill
4. Click OK to close the dialog box.

The character will fill the cell, and expands or shrinks as the column width is adjusted.

### Example 1: In-Cell Bar Chart

If you don't have a newer version of Excel, with data bars, you can use the REPT function to create a simple in-cell bar chart. For example, to create a bar chart for a target of 100:

1. In cell B3, type 100
2. In cell C3, enter the following formula:
=REPT("n",B3/5)
3. Format cell C3 with Wingdings font (I used font size 9)
4. Adjust column C's width to fit the resulting bar.
5. Change the number in cell B3, and the chart will change.

I added conditional formatting in this example, to highlight quantities less than 60.

### Example 2: In-Cell Dot Chart

Instead of bar charts, you can use the REPT function to create a simple in-cell dot chart. For example, to create a dot chart for a target of 100:

1. In cell B3, type 100
2. In cell C3, enter the following formula:
=REPT("  ",B3/5-1) & "o"
3. Adjust column C's width to fit the resulting bar.
4. Change the number in cell B3, and the dot location in the chart will change.

### Example 3: Keep a Simple Tally

If you lost your cribbage board, or are counting the days until your next vacation, you can use a simple tally to keep track of the score, or the passing days.

To create a tally with the REPT function:

1. In cell B3, type your target amount, e.g. 25
2. In cell C3, enter the following formula:
=REPT("tttt ",INT(B3/5)) & REPT("l",MOD(B3,5))
3. Format cell C3 with Comic Sans font, or another font with a straight "t". (Finally -- a use for Comic Sans!)
4. Adjust column C's width to fit the resulting bar. If the target number is high, you can increase the row height, and format for Wrap Text
5. Change the number in cell B3, and the tally will change.

The formula result shows one group of t's, for every 5 items in the count -- INT(B3/5).
If there is a remainder, after dividing the count by 5, that amount is displayed at the end, as lower case L's -- MOD(B3,5).

### Example 4: Find the Last Text Item in a Column

Combined with VLOOKUP, you can use the REPT function to find the last text item in a column. For example, with text items in column D, use this formula to find the last item:
=VLOOKUP(REPT("z",255),D:D1)
The REPT function in the formula creates a text string at the end of the alphabet, and VLOOKUP won't be able to find that string. So, with approximate match, it returns the last text item in the list.

To see the formulas used in today's examples, you can download the REPT function sample workbook. The file is zipped, and is in Excel 2007 file format.

### Watch the REPT Video

To see a demonstration of the examples in the REPT function sample workbook, you can watch this short Excel video tutorial.

_____________

## 9 thoughts on “30 Excel Functions in 30 Days: 15 – REPT”

1. Debra,

I often use the REPT() function as an in-cell replacement for nested IF() functions, one that does not require a lookup table.

For example, this nest:

`=IF(A2="red","North", IF(A2="white","South", IF(A2="blue","East", "Other")))`

could be replaced with this:

`=REPT("North",A2="red") & REPT("South",A2="white") & REPT("East",A2="blue")`

This method takes advantage of the empty string produced by REPT() when number_times is zero, and the fact that TRUE is represented internally by the Excel worksheet calculation engine as 1 and False by 0.

1. Dave says:

Thank you, so much for sharing.
I know this is an old post, but I can't/won't resist comment.
Excellent tip! This can solve a multitude of tedious formula builds, and, I suspect, opens the door to other creative solutions in other formulas. I'm going to remember this one. I like it better than CHOOSE. Would have never thought of this one in a million years.
Glad I came to this site.

2. Genecy Monte says:

Thank you very much for sharing this use of REPT function as a conditional. It will help a lot.. =)

2. [...] Excel REPT Formula – 30 days 30 formulas [...]

3. Bjacobowski says:

Daniel: Interesting use of REPT, I had never seen that one.

I tend to use REPT for something like an ID that requires 10 characters, but the data your given leaves off leading zeros: =RIGHT(REPT(0,10)&A1,10)

4. Hugo says:

@Daniel Ferry
Appriciate this nice idea.
But at the end there's the missing "Other" in your REPT hint.
How to be appended?

5. Some good stuff here. I always wondered what the horizontal alignment "Fill" was for, glad to finally know. The tally example is something I can't wait to try out and finding the last text item in a column is a handy trick to have in your back pocket.

6. Luke Wisbey says:

REPT is also very useful when it comes to sub string extraction.

Consider A1:A3 of:

ASAJK-ASAS-SHH-D1234569-KJAKJSA

We wish to retrieve the 4th delimited value in each string to B1:B3 respectively

B1:
=MID(LEFT(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",4))-1),FIND("^^",SUBSTITUTE(A1,"-","^^",3))+1,255)

Or, we could use a REPT based method:

B1:
=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),1+3*LEN(A1),LEN(A1)))

The advantage of the latter is that we can adapt 1+n*LEN to relate to whichever term we're interested in – from 1 to n, eg:

=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),1+(\$C\$1-1)*LEN(A1),LEN(A1)))
where C1 holds n (eg 4 in this instance)

It's true we can do the same with the more traditional approach for items 2 to n but we can not use that same approach for first term unlike the above – ie the above is truly flexible.

This site uses Akismet to reduce spam. Learn how your comment data is processed.