How to Use REPT Function in Google Sheets [Practical Example]

How to Use REPT Function in Google Sheets

I don’t think using REPT function alone is not worth enough in Google Sheets. But with functions like LEN, ARRAY, COUNTIF, Google Sheets REPT function can produce wonderful results. Let’s see how to use REPT function in Google Sheets and some practical use of it.


REPT(text_to_repeat, number_of_repetitions)


The purpose of REPT function is to repeat given text to a specific number of times. But in real sense, REPT function can repeat numeric values also.

How to Use REPT Function in Google Sheets

First see some basic examples to the use of Google Sheets REPT function. See some sample REPT formulas below.

REPT Function Basic Examples

Practical Use of Google Sheets REPT Formula

I’ve two examples here. One with COUNTIF and the other REPT function is making use of the SPLIT and ARRAYFORMULA combination.

Example to the Use of COUNTIF with REPT

Here I have a list of participants in a painting competition. You can see their participation status visualised in Column G with the help of asterics.

Use of REPT function with COUNTIF

I’ve used here Google Sheets REPT function with COUNTIF. In this example, the COUNTIF function counts the occurrence of “Y” in the range B3:F3. It returns 5 for this row. So the REPT function repeat the “*” 5 times. Then the formula copied and pasted to downwards.

Actually the above formula is like this without COUNTIF.


Example to the Use of SPLIT and ARRAYFORMULA combination with REPT

This is another common use of Google Sheets REPT function. This combination can repeat any column range to the given number of times.

Repeat a Column N Times

In the above example, the combination of functions repeat text as well as numeric value in Column A, 4 times.

Formula Explanation:

First see the REPT Formula Part. What it does?


The above formula returns a raw data similar to below.

Repeat with Split

Then we make use of the SPLIT function to split the text to columns using “;” as delimiter. This way you can use REPT function in Google Sheets.

I will be back with few more advanced use of REPT function in coming tutorials. Hope you have enjoyed this session.


Please enter your comment!
Please enter your name here