How to Use the REPT() Function in Google Sheets?

by | Dec 20, 2023 | How-To/Tips, Internet

The REPT() function in Google Sheets is used to repeat some inputs or actions several times. It is helpful in generating sample data, formatting cells and creating tables. In this guide, we will learn how to use the REPT() function in Google Sheets.

How to Use the REPT() Function in Google Sheets?

The following is the syntax used for the REPT() function in Google Sheets:

REPT(text_to_repeat, number_of_repetitions)

Where, 

text-to_repeat is the argument used for the string we want to repeat.

number_of_repetitions is the value for the number of repetitions we want to repeat the string.

Example 1: Use of Basic REPT() Function

Following is a very simple example to better understand the use of the REPT() function, in which we want to repeat the text Hello for 10 times in the Google Sheets.

For this, we will use the “Hello” for the text_to_repeat argument and 10 as the number_of_repititions argument in the REPT() function as follows:

REPT( "Hello", 10)

In the above example, we see that the text Hello is repeated ten times in the cell, but there is no space between every text. To get the space between the texts to be repeated, we need to add space before or after the text in the REPT() function as follows:

Example 2: Use of REPT() Function to Repeat Action Over Multiple Cells

Most of the time we need to repeat the string over multiple cells instead of in a single cell. For this, we use the REPT() function with the SPLIT() function:

SPLIT(REPT(A1&"?",B1),"?"))

Where,

The SPLIT() function divides the text string over the given number and arranged them into their own cells.

The Queen symbol “ ? “ is used by the REPT() function to divide repeated strings and use for the special identifier.

A1 is the reference cell containing the text to repeat.

B1 is the reference cell for the number of times we want to repeat the text:

To convert the arrangement of the repeated text into the column, we use the TRANSPOSE() function in combination with the SPLIT() function and the REPT function. The TRANSPOSE() function is used to convert the columns into rows and vice versa:

Example 3: Use of REPT() Function to Repeat Images in Google Sheets

The REPT()function can also be used to repeat a specific image over a row. Suppose we want to repeat the Google icon image in Google Sheets, we will use the following syntax:

ArrayFormula(IMAGE(SPLIT(REPT("https://www.google.com/favicon.ico"&"?",8),"?"))

Where,

ArrayFormula is used to return the values into multiple rows.

IMAGE() function is used to resize the image automatically and fit into the cells.

SPLIT() function divides the text string over the given number and arranged them into their own cells.

The Queen symbol “ ? “ is used by the REPT() function to divide repeated strings and use for the special identifier.

Example 4: Using REPT() Function With CHART() Function

We can also use the REPT() function in combination with the CHART() function to repeat the special character multiple times in the Google Sheets. The syntax used for this is as follows:

REPT(CHAR(unicode-number), number-of-repetitions)

Where,

The CHAR() function is used to convert the numerical values into the corresponding character.

unicode-number is the argument used by CHAR() function and is the numerical value we want to convert into a special character.

number-of-repetitions is the argument for the REPT() function to repeat the character in number of times.

For our example, we will the following syntax:

RREPT(CHAR(10074),A1)

Where,

10074 is the argument for the CHART() function and a numerical value associated with the special character.

A1 is the cell reference used for the number-of-repetitions argument in the REPT() function to repeat the character:

Similarly, we can use the above syntax for different characters to repeat as follows:

Pro Tips:

The following are the most important things you must keep in mind while using the REPT() function in Google Sheets.

  1. The output of the REPT() function is a string instead of any numerical value. We cannot put this value into other formulas.
  2. If we use the number_of_repitition argument in the REPT() function as zero, the output will be an empty string in the cells.
  3. The number_of_repitition argument will always be positive. The negative value in this argument will result in an error.
  4. If we want to get space between the strings, we must have to add space before or after the string in the REPT() function as mentioned in example 1.
  5. The number_of_repitition argument cannot be more than 32,000 characters, as it is the character limit of a cell.

Conclusion

The REPT() function in Google Sheets is used to repeat some inputs or actions several times. It helps to generate sample data, format cells and create tables. The basic arguments used in the REPT() function are text_to_repeat, and number_of_repetitions. 

The first argument is for the text or string to repeat, and the second is for the number of times we want to repeat the string. The number_of_repetitions argument in the REPT() function couldn’t be a negative value.