# How to use the REPT function

**What is the REPT function?**

The REPT function repeats a specific string a given number of times.

#### Table of Contents

## 1. Introduction

**Which category is the REPT function in?**

The REPT function is in the TEXT category.

**When was the REPT function introduced in Excel?**

The REPT function was introduced in Excel 2007 as part of the Excel 2007 update to the Office suite. This version of Excel brought a significant expansion of available functions and formulas, including text functions like REPT. Since its introduction REPT has been available in all subsequent versions of Excel including Excel for Microsoft 365.

## 2. REPT Function Syntax

REPT(*text, number_times*)

text |
Required. The value you want to repeat. |

number_times |
Required. The number of times you want to repeat the value. |

## 3. Example

The image above contains 4 different examples, the source values are in cells B3:B6. The number of times each source value is repeated are in cells C3:C6. The results are in cells D3:D6

The first example has source value "A" specified in cell B3. The number of times the source value is repeated is specified in cell C3.

Formula in cell D3:

The formula in cell D3 returns "AAA" which shows that the REPT function repeats "A" three times.

The second example uses source value "red" specified in cell B4. The number of times the source value is repeated is specified in cell C4 which contains 2.

Formula in cell D4:

The formula in cell D4 returns "redred" meaning the REPT function repeats "red" twice.

The third example uses source value "white" specified in cell B5. The number of times the source value is repeated is specified in cell C5 which contains 0 (zero).

Formula in cell D5:

The formula in cell D5 returns "" (nothing) meaning the REPT function doesn't repeat "white" at all.

The fourth example uses source value "black" specified in cell B6. The number of times the source value is repeated is specified in cell C6 which contains -1 (negative number).

Formula in cell D6:

The formula in cell D6 returns #VALUE! which is an error value meaning the REPT function can't handle negative numbers.

## 4. Basic bar chart

The image above demonstrates how to create a basic bar chart using the REPT function. It repeats any character or symbol you like, letting you quickly compare numbers based on the length of the horizontal repeated characters.

Formula in cell D3:

=REPT(CHAR(149),C3)

The formula in cell D3 repeats character • three times based on the specified number in cell C3. The symbols in D3:D7 makes it easier to compare the relative sizes of the scores in cells C3:C7.

### 4.1 Explaining formula

#### Step 1 - Create a large dot

The CHAR function returns a character or symbol based on a number between 1 and 255.

CHAR(*text*)

CHAR(149)

returns •

#### Step 2 - Repeat large dot based on given number

REPT(CHAR(149),C3)

becomes

REPT("•",C3)

becomes

REPT("•",3)

and returns "•••".

## 5. REPT function alternative

The ampersand character concatenates values, this can be used to repeat values. The downside is that you need to repeat the ampersand character as many times as you want the value repeated.

Formula in cell D3:

=B3&B3&B3

The formula in cell D3 concatenates the specified value in cell B3 three times, in other words, repeating the value three times.

The image below shows a different formula that also repeats a given value a specific number of times. The downside is that it is larger and more complicated than the REPT function.

Formula in cell D4:

=TEXTJOIN("",TRUE,INDEX(B4,{1,1,1}))

### 5.1 Explaining formula

#### Step 1 - Create array

The curly brackets let you create an array of values in an Excel formula. The comma and semicolon are delimiting characters you can use, the comma separates values horizontally or column-wise. The semicolon delimits values vertically or row-wise.

The comma and semicolon are determined by your computer's regional settings.

{1,1,1}

#### Step 2 - Repeat value in cell B4

The INDEX function gets a value from a cell range or array based on a row and column number (optional).

INDEX(B4,{1,1,1})

becomes

INDEX("B",{1,1,1})

and returns {"B", "B", "B"}.

#### Step 3 - Join values in the array

The TEXTJOIN function concatenates values.

TEXTJOIN(*delimiter*, *ignore_empty*, *text1*, *[text2]*, ...)

TEXTJOIN("",TRUE,INDEX(B4,{1,1,1}))

becomes

TEXTJOIN("",TRUE,{"B", "B", "B"})

and returns "BBB".

## 6. REPT function array

This example shows a formula that repeats all values in cells B3:B5 based on the corresponding numbers on the same rows in cells C3:C5, and returns the result as a single string in cell E3.

For example, "A" is repeated three times, "B" is repeated twice, and "C" is not repeated, only one instance is displayed.

Formula in cell E3:

=TEXTJOIN("", TRUE, REPT(B3:B5, C3:C5))

The result is a text string "AAABBC" which is displayed in cell E3.

### 6.1 Explaining formula

#### Step 1 - Repeat values in an array based on corresponding numbers on the same row

REPT(B3:B5,C3:C5)

becomes

REPT({"A"; "B"; "C"}, {"3"; "2"; "1"})

and returns

{"AAA"; "BB"; "C"}

#### Step 2 - Join values without a delimiter

The TEXTJOIN function concatenates values.

TEXTJOIN(*delimiter*, *ignore_empty*, *text1*, *[text2]*, ...)

TEXTJOIN("",TRUE,REPT(B3:B5,C3:C5))

becomes

TEXTJOIN("",TRUE,{"AAA"; "BB"; "C"})

and returns

"AAABBC".

## 7. REPT function based on list

The image above shows a formula in cell D3 that repeats the value in cell B3 if it exists in the list in F3:F7.

=IF(COUNTIF($F$3:$F$7,B3),REPT(B3,C3),"-")

### 7.1 Explaining formula

#### Step 1 - Repeat value

REPT(B3,C3)

becomes

REPT("A", 3)

and returns "AAA".

#### Step 2 - Check if value is in the list

The COUNTIF function counts the number of cells that is equal to a condition.

COUNTIF(*range*, *criteria*)

COUNTIF($F$3:$F$7,B3)

becomes

COUNTIF({"A"; "C"; "D"; "F"; "H"},"A")

and returns 1. Cell value "A" is found once in the list.

#### Step 3 - Evaluate IF function

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(COUNTIF($F$3:$F$7,B3),REPT(B3,C3),"-")

becomes

IF(1, REPT(B3, C3), "-")

becomes

IF(1, REPT("A", 3), "-")

becomes

IF(1, "AAA", "-")

and returns "AAA".

## 8 REPT function based on list and concatenated

Formula in cell G3:

=TEXTJOIN(,TRUE,IF(COUNTIF($E$3:$E$7,B3:B10),REPT(B3:B10,C3:C10),""))

### 8.1 Explaining formula

#### Step 1 - Repeat value

REPT(B3:B10, C3:C10)

becomes

REPT({"A"; "B"; "C"; "D"; "E"; "F"; "G"; "H"},{3; 8; 1; 2; 1; 2; 4; 2})

and returns

{"AAA"; "BBBBBBBB"; "C"; "DD"; "E"; "FF"; "GGGG"; "HH"}.

#### Step 2 - Check if value is in the list

The COUNTIF function counts the number of cells that is equal to a condition.

COUNTIF(*range*, *criteria*)

COUNTIF($F$3:$F$7,B3:B10)

becomes

COUNTIF({"A"; "C"; "D"; "F"; "H"}, {"A"; "B"; "C"; "D"; "E"; "F"; "G"; "H"})

and returns

{1; 0; 1; 1; 0; 1; 0; 1}

#### Step 3 - Evaluate IF function

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

IF(COUNTIF($F$3:$F$7,B3),REPT(B3,C3),"")

becomes

IF({1; 0; 1; 1; 0; 1; 0; 1}, {"AAA"; "BBBBBBBB"; "C"; "DD"; "E"; "FF"; "GGGG"; "HH"},"")

and returns

{"AAA"; ""; "C"; "DD"; ""; "FF"; ""; "HH"}

#### Step 4 - Concatenate values in array

The TEXTJOIN function allows you to combine text strings from multiple cell ranges and also use delimiting characters if you like.

TEXTJOIN(*delimiter*, *ignore_empty*, *text1*, *[text2]*, ...)

TEXTJOIN(,TRUE,IF(COUNTIF($E$3:$E$7,B3:B10),REPT(B3:B10,C3:C10),""))

becomes

TEXTJOIN(,TRUE,{"AAA"; ""; "C"; "DD"; ""; "FF"; ""; "HH"})

and returns "AAACDDFFHH".

## 8. REPT function error

- 0 (zero) makes the REPT function return nothing.
- A negative number or a text string returns a #VALUE error.

### Useful links

REPT function - Microsoft support

Excel REPT Function Examples

### 'REPT' function examples

This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]

This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]

What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]

### Functions in 'Text' category

The REPT function function is one of 29 functions in the 'Text' category.

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse html character entities instead of less than and larger than signs.

< becomes < and > becomes >

How to add VBA code to your comment[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

How to add a picture to your comment:Upload picture to postimage.org or imgur

Paste image link to your comment.

Contact OscarYou can contact me through this contact form