The REPT function has many uses in Google Sheets, though its core purpose is to repeat a specified character or string N times.
Here is the syntax of the function:
REPT(text_to_be_repeated, number_of_repetitions)Arguments:
text_to_be_repeated: Specifies the character or string that you want to repeat.number_of_repetitions: A positive number specifying the number of times (n) to repeat thetext_to_be_repeated.
Google Sheets limits this function to 32,000 characters (please refer to the official documentation HERE for more details).
Both of the above arguments can be cell references or hardcoded within the formula.
Basic Examples
Example 1:
When you have missing data and need to fill such cells with placeholders, you can use the REPT function as follows:
=REPT("-",10)This will repeat the dash character 10 times in the applied cell.
Example 2:
When printing reports, you can insert decorative elements at the top and bottom of the page by repeating a character. For example, use =REPT("+", 75) to create a line of plus signs for a visual break.
=REPT("✄ ", 100)This will provide a visual clue for where the report should be cut into separate sections.
Examples of Using the REPT Function within an Array
In the following examples, the argument number_of_repitions will be a range reference. In that scenario, we should use the ARRAYFORMULA function with the REPT function.
Creating a Small Bar Chart
We can utilize the SPARKLINE function in Google Sheets to create a small bar chart. Alternatively, you can achieve the same result using a combination of the RANK and REPT functions.
Suppose you need to generate a bar chart for the data in the range A2:A6. You can employ either of the following formulas:
Rept:
=ArrayFormula(REPT("██", RANK(A2:A6, A2:A6, TRUE)))In REPT, the RANK function assigns ranks to values in the array, with the lowest value in the data receiving a rank of 1.
Sparkline:
=MAP(A2:A6, LAMBDA(r, SPARKLINE(r, {"charttype", "bar"; "max", MAX(A2:A6); "color1", "red"})))
How do they differ?
The SPARKLINE function necessitates the MAP lambda helper function to deliver the bar chart in each row within the array, whereas REPT does not require this. This characteristic makes the REPT function easier to comprehend.
Another difference is that the SPARKLINE bar chart adjusts with the column width and height, whereas the REPT formula won’t.
To change the bar color, you need to specify the color within the SPARKLINE formula, whereas, in REPT, you can directly change the bar color by changing the font color since the bars are just characters.
Finally, in REPT, you can use different sizes and types of characters to customize the bars.
Note: The REPT-based bars will be proportionate to ranks, not the original data points.
Status of Participation
Here I have a list of participants in a painting competition. Their names are in A3:A9 and the categories of competition are in B2:F2.
Below, under each column, “Y” represents participation. You can use the following formula to visualize the status of participation of the first competitor in cell G3:
=REPT("*", COUNTIF(B3:F3, "Y"))
This REPT formula utilizes the COUNTIF output to repeat the asterisk characters. To obtain the status of other participants, you can either copy-paste this formula down or employ the following BYROW formula:
=BYROW(B3:F9, LAMBDA(r, REPT("*", COUNTIF(r, "Y"))))REPT with SPLIT to Repeat Labels
Sometimes, we may need to repeat a column N times for printing purposes.
Assume we have generated barcodes for a few items in a column and want them to print N times. In that case, we can use the REPT function to repeat the column.
The basic example below will give you an idea of using the REPT function in Google Sheets with a combination of SPLIT and ARRAYFORMULA.
We will repeat the values ‘n’ times, then SPLIT those repeated values.
However, to split, we need to specify a delimiter. But the REPT function does not insert spaces or any other delimiters between repetitions of text_to_be_repeated.
Here, we will add a “;” after each repeated value to separate them (you can also use spaces), and then SPLIT the output.
=ArrayFormula(SPLIT(REPT(A2:A7&";", 4), ";"))
