# 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:

- In a cell, type the character(s) that you want as the fill, e.g. a hyphen or period
- With the cell selected, press Ctrl + 1, to open the Format Cells window
- Click the Alignment tab, and from the Horizontal setting, select Fill
- 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:

- In cell B3, type 100
- In cell C3, enter the following formula:

**=REPT(“n”,B3/5)**

- Format cell C3 with Wingdings font (I used font size 9)
- Adjust column C’s width to fit the resulting bar.
- 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:

- In cell B3, type 100
- In cell C3, enter the following formula:

**=REPT(” “,B3/5-1) & “o”**

- Adjust column C’s width to fit the resulting bar.
- 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:

- In cell B3, type your target amount, e.g. 25
- In cell C3, enter the following formula:

**=REPT(“tttt “,INT(B3/5)) & REPT(“l”,MOD(B3,5))**

- Format cell C3 with Comic Sans font, or another font with a straight “t”. (Finally — a use for Comic Sans!)
- 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
- 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.

### Download the REPT Function File

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.

YouTube link: **Get Excel Version Number with REPT Function**

_____________

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.

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.

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

[…] Excel REPT Formula – 30 days 30 formulas […]

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)

@Daniel Ferry

Appriciate this nice idea.

But at the end there’s the missing “Other” in your REPT hint.

How to be appended?

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.

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

Consider A1:A3 of:

JAKJSAKS-ASHJAH-SJHJ-A123-ASKAJSA

ASAJK-ASAS-SHH-D1234569-KJAKJSA

AKJKASJ-ASJKA-A-ASKKASJ-A-ASJAHS

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

We could use a traditional:

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.

Thanks for all the comments and examples for the REPT function!