30 Excel Functions in 30 Days: 15 – REPT

Excel REPT function

Yesterday, in the 30XL30D challenge, we took things easy, with the T function.

REPT Function

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.

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. 😉

Rept00

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.

Rept00a

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.

In-Cell Bar Chart with REPT function
In-Cell Bar Chart with REPT function

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.

Rept02

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.

Rept03a

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).

Rept03b

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:D,1)

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.

Rept04

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.

_____________

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. 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. Thank you very much for sharing this use of REPT function as a conditional. It will help a lot.. =)

  2. 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)

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

  4. 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.

  5. 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.

Leave a Reply to Dave Cancel reply

Your email address will not be published.

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