The REPT function in Google Sheets is used to repeat an expression a set number of times.
The REPT formula to repeat “Go! ” three times is:
=REPT("Go! ", 3)
Notice the additional space added after the exclamation point, so that there is a space between the repeated values in the output.
More REPT Function Examples
Column A contains the values you want to repeat.
Column B contains the number of repetitions.
The formula is then:
=REPT(A2,B2)
As you can see, for each row the value in A is repeated per the number in column B and joined into one long string.
REPT Function in Google Sheets: Syntax
The REPT function takes two arguments and both are required.
=REPT(text_to_repeat, number_of_repetitions)
text_to_repeat
This is a text string or cell reference that you want to repeat.
number_of_repetitions
This is a positive integer indicating how many times you want to repeat the input text.
Notes on using the REPT function
- The return value (the output of the REPT function) is a string value in a single cell
- Setting the number of repetitions to 0 results in a cell containing a blank string (not a true blank cell though. ISBLANK will still give FALSE)
- Setting the number of repetitions to -1 results in a #VALUE! error
- If you want spaces between the repeated text strings, you must add that as the final character of the input string (as shown in the
"Go! "
example above). This results in a final trailing space, which can be removed with the TRIM function if required - The
number_of_repetitions
can’t exceed the character limit of a cell: 32,000 characters. If it does, you’ll see a #VALUE! error - Ranges can be repeated by wrapping the REPT function in an Array Formula
REPT function template
Click here to open a view-only copy >>
Feel free to make a copy: File > Make a copy…
If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open it in an Incognito window you’ll be able to see it.
You can also read about it in the Google documentation.
Using The REPT Function For Repeated Values In Separate Cells
What if you don’t want to create a long string of repeated values in a single cell, but instead want to output the repeated value across a range of cells, such that each value is in its own cell?
That’s possible with this formula, assuming the value is in cell A1 and the number of repetitions is in cell B1:
=TRANSPOSE(SPLIT(REPT(A1&"♕",B1),"♕"))
This particular formula outputs 10 values “text” in a column. (You can remove the transpose function if you want the output across a row.)
The Queen symbol “♕” is added to the end of the repeated value to act as a unique value used by the SPLIT function to divide the repeated string.
Note: you can also achieve this affect with the SEQUENCE function:
=ArrayFormula(TEXT(SEQUENCE(B1),"")&A1)
This particular variation uses the SEQUENCE function to output an array 1,2,3,…10. The TEXT function then “tricks” all of these to become empty strings. We then concatenate the repeated value from cell A1 onto this string. Finally, the Array Formula ensures that the output is an array.
Repeated Images with REPT Formula in Google Sheets
This REPT formula will repeat the specified image across a row. It uses the same Queen symbol “♕” trick as the previous formula.
=ArrayFormula(IMAGE(SPLIT(REPT("https://www.google.com/favicon.ico"&"♕",5),"♕")))
The output looks like this:
Using REPT As Logical Formulas
The REPT function can be used instead of IF formulas in specific situations.
It’s quite a clever trick that works because TRUE is equivalent to the number 1, and FALSE to the number 0, in formulas.
So, by putting a logical test, which evaluates to TRUE or FALSE, equivalent 1 or 0, in the number of repetitions argument, we can either show 1 value or 0 values using REPT.
For example, this formula identifies values over $1,000:
=REPT("Over $1,000",A1 > 1000)
And the output looks like this:
This formula works because the logical test checks if the value in cell A1 is greater than $1,000. If the result is TRUE, it’s interpreted as a value of 1, which then repeats the text in the REPT formula once. So the output is “Over $1,000”.
If the value is less than or equal to $1,000, then the test evaluates to FALSE, equivalent to 0 input for the REPT formula, so the output is a blank string in the cell.
This technique can be taken a step further to check multiple conditions are TRUE.
For example, the following formula checks whether the value is over $1,000 AND comes from Client A:
=REPT("Over $1,000 and Client A",(B1>1000)*(A1="Client A"))
The two logical tests evaluate to TRUE or FALSE, which are interpreted as 1 or 0 by the formula.
When the value is greater than $1,000 and from Client A, it’s equivalent to:
TRUE * TRUE = 1 * 1 = 1
In which case, the formula outputs the string “Over $1,000 and Client A”
Multiplication using “*” as shown above is for the AND case, where both conditions are true.
To do the OR case, you use addition “+”. If either value is TRUE and the other FALSE, it’s equivalent to 1 + 0 = 1. When both are true it’s equivalent to 1 + 1 = 2, so the REPT would repeat the value twice which we don’t want.
To fix this, wrap it with a MIN formula to set to 1 if the value is above 1.
=REPT("Over $1,000 and Client A",MIN((B1>1000)+(A1="Client A"),1)
In Cell Charts with REPT Function in Google Sheets
Bar chart with REPT Function
The formula to do this is:
=REPT(CHAR(10074),A1)
The CHAR formula generates a special character based on the number.
You can use any character as the repeated symbol, for example you could use tacos with CHAR(127790):
Vertical Bar Chart with REPT function
You can easily extend the technique above to display the bar chart in a single cell, with a vertical orientation, using this formula:
=ArrayFormula( JOIN( CHAR(10), REPT( CHAR(9679),A1:A6)))
which looks like this (using dots as the repeated symbol):
These charts are known as dot plots and you can find out more about how to create them here, including how to make them multi-colored: Dot Plots in Google Sheets
Dynamic Width Bar Charts Using REPT function
This is an interesting, although not particularly useful, formula which uses the CELL function to access the width of a cell and then sets the REPT to repeat the value to match the width of the cell:
The formula to do this is:
=REPT(CHAR(10074),ROUND((A2/MAX($A$2:$A$4))*(CELL("width",$B$1)*1.25),0))
It uses the random number generator and the spreadsheet calculation setting to be “On change and every minute” to force the CELL function to re-calculate the width value each time there is a change.
The 1.25 factor at the end is simply to adjust the largest bar to fit the full width. It depends on the character you use to repeat and how wide it is. Feel free to experiment.
Padding Strings With The REPT Function
REPT can also be used to pad text strings.
With a value in A1, this formula adds a variable number of underscores to the end of the string:
=A1&REPT("_",20-LEN(A1))
If you want to have the text strings to have equal widths you’ll also need to use a font where the letters are equally spaced, like SOURCE CODE PRO.
This approach can also be achieved with numbers, although it’s much better to use custom number formatting, because that preserves the value as type number instead of converting to a string.
Thanks, Ben, for the excellent tutorial on Google Script Query function. That has certainly simplified my work.
I have a doubt over the topic. How can you add an additional column to the query table, so that the added column works on the columns of the query result and has exactly same number of rows of the query result.
I will make it more clear.
I have a table with date (A), item (B), number bought (C) and buy rate (D), amount spent (E), number sold (F) and sell rate (G), amount gained (H) and the credit (or debit) (I=E-H). This I picked up from the transaction data through a query. Now, against each row, I wanted to add two columns for balance number of item and the effective rate of the item on that date. After the query result, I would have added the columns as with formula “=SUMPRODUCT(C:C,B:B=B2,ROW(B:B)<=ROW())-SUMPRODUCT(F:F,B:B=B2,ROW(B:B)<=ROW())" for column J and "=SUMPRODUCT(I:I,B:B=B2,ROW(B:B)<=ROW())/J2" for column K, which works fine. But since the query result is dynamic and the number of rows are not known beforehand, this leaves the column as stubs or blanks. How can I solve this issue, by joining the formula as part of the query?
A solution is awaited. Your article on Query Total was quite illuminating, but I could not transcend it to this problem.
Regards,
Pravin Kumar.
Hi Pravin Kumar Raja,
I won’t even lie I’ve read your entire post or that I know what you are talking about but maybe something like this will work for you? It works for me but it uses filter() instead of query(), I am just starting with google sheets so I don’t even know how to use query() or the functions you mentioned but it seems they serve similar purposes on a glance.
The formula will be like this:
=filter({B1:B2\A1:A2\arrayformula(H4:H5*$A$5)};$A$4:$A$5″wtf”)
You will need some values in A1:B2 , H4:H5 and A5 for it to work.
you can add it them like so: in A1 type in ={1/10;2/20} and in H4 ={10;50} and some number in A5 like =100
Please note that you might want to change commas to semicolons, backslashes (or something like that) and all that nonesense.. I am not really sure how to do it, it annoys me and I don’t want to mess something up so I didn’t change to other locale to test it. Here is more info about what has to be done for arrays specifically:
https://support.google.com/docs/answer/6208276?hl=en
or google “Using arrays in Google Sheets”
So what it does it basically showcases how you can reorder columns and add new ones in filter() function. Apparently you can provide filter() function with an array rather than a range as a first parameter, this is a resulting table that will be shown. Filtering is done based on the next ones, you can use something that always evaluates to true (this is what the second parameter “$A$4:$A$5″wtf”” tries to accomplish) but I think it has to match the size of an array you pass into first parameter.
In case you wanted to go the route you are going already (which is basically adding additional columns separately after a query) I wanted to suggest something like A1:A range but I saw you are doing it already. You mentioned you ran into an issue where columns are left as stubs or blanks, what do you mean by that? Why is this not desired? Also filter() seems to exclude empty rows from the result if this is what you are after.. maybe this can be used with transpose() to remove empty columns?
I forgot to mention, you can also “attach” a second table after your query in same formula but it seems messy because the table columns/rows size(depending if you add it horizontally/vertically) need to match.
If you want to know a size of a given table/array/query or want to pad it to a given size here is some idea about how to do it contained within this answer (there is few answers but I’ve linked to a specific one), it uses a variation of a formula that is present in post we’re currently commenting:
https://stackoverflow.com/a/54795616
Can you know the iteration number?
something like:
=REPT(“Iteration:” &i&” “, 3);
would print Iteration:1 Iteration:2 Iteration:3
Hi Iurie,
please don’t shoot me, I’m not a native speaker so I might misunderstood your question. Does it have to be done using REPT() function specifically? If so I have no idea but if you just need to iterate over some sequence maybe this will work for you?:
=arrayformula(“Iteration: “&column(A1:C1))
This will put each value in a separate cell, I am sure t hey can be merged together somehow if needed. Apparently when used in arrayformula column() and row() returns current position in range specifed in arrayformula. Here’s where I found it:
https://webapps.stackexchange.com/a/102589
or google for “How can I create a formula to generate a range in Google Sheets?” if you don’t like clicking links.
I think there is a lot more ways to do that but I only just learnt it recently so I might just add confusion.
Later note: Oh actually I found another way on this website:
https://www.benlcollins.com/spreadsheets/sequence-function/
or search for “Build Numbered Lists With The Amazing SEQUENCE Function” or “generate range” in a searchbar on this website.
I found 3 Char codes I like for Rept bar charts:
9608 – thick line with no gaps
9644 – thin line with no gaps
9648 – rhomboid with gaps
By gaps, I mean space between the characters. With no gaps, it looks like a solid line, at least on the fonts I tried.