What is REPT Function in Google Sheets?
The REPT function in Google Sheets is used to repeat a text a specified number of times. It helps us create patterns, get spaces, filler characters, or duplicate strings. The formula has two arguments: the text we wish to repeat and the number of times it should be repeated. You can combine the REPT formula with other functions to create dynamic repetitions based on specific conditions.
To understand it in a simple form, let us enter the following formula in a cell in the example below.
=REPT(“Hi!”, 4).
The cell will display the text “Hi!Hi!Hi!Hi!” because the word “Hi!” is repeated four times.
Key Takeaways
- REPT in Google Sheets is used to repeat a particular text string a specified number of times.
- The syntax of REPT is as follows:
=REPT(text, number_of_times)
text: The string that you want to repeat.
number_of_times: The number of times you want to repeat the text.
- The number of characters can be repeated to approximately 32,000 times. If the total length of the text exceeds this limit, it will be truncated.
- REPT is often used to create visual markings, such as stars and checkmarks in cells.
- You can use REPT for dynamic visualizations that change with change in the data, such as using rating stars or color-coded bars.
Syntax
As you know by now, the REPT function is very helpful to repeat any text or to add spaces a fixed number of times. To dig deeper into its functioning, let us first understand the syntax. The syntax of the REPT function is as follows:
=REPT(texttorepeat, numberofrepetitions)
- texttorepeat: This is the text that we wish to repeat
- numberofrepetitions: Number of times you want to repeat the text.
How to Use REPT Function in Google Sheets?
There are two ways to enter the REPT function in Google Sheets.
Entering the REPT Function Manually
Let us look at a simple way where we want to repeat some text manually. We have some text in a cell in Column C.
Step 1: Select the cell where you want to show the output. Here, we select C5 and C7. Now, in cell C5, enter the following formula.
=REPT(
Step 2: To repeat the text in cell C1, use the following arguments: =REPT(C1,2) and close the parenthesis.
Step 3: Press Enter. You can try the formula again in cell C7 with different set of arguments, as shown below.
Using the Google Menu Bar
- We can also enter the same function using the Google Menu bar.
- Go to the menu bar and click on “Insert” ➝ “Function” ➝ “TEXT” ➝ “REPT.”
- Enter the required arguments. Close the bracket and press the “Enter” key.
Note: You can combine REPT with other functions or formulas to create dynamic repetitions in your sheet.
Examples
Now that we have absorbed some basic details about the REPT function, let’s try to understand it in detail with some interesting REPT in Google Sheets examples.
Example #1
We have a list of students who have taken a test. The teacher enters their marks on a scale of 1 to 5. In Column C, we print the corresponding stars. Let’s see how to use the REPT function to do this
Step 1: Enter the given data in Columns A and B of a sheet.
Step 2: Now, insert the corresponding stars in Column C. In Google Sheets, the character number for a star symbol is 9733.
We use the CHAR function with the above Unicode value to input the star symbol into a cell. So, to print a star in a cell, we use =CHAR(9733). It will display the star in the cell.
It can be combined with the REPT function to provide a visual rating. Enter the following function in cell C2.
=REPT(CHAR(9733),B2)
Here, we get the number in cell B2 and repeat the symbol represented by CHAR(9733) accordingly.
Step 3: Press Enter and observe cell C2. The number of stars is determined by the number in B2.
Step 4: Now, drag the REPT in the Google Sheets formula up to cell C8 and check how the stars are beautifully displayed according to the number specified in Column B.
Thus, combining REPT effectively with CHAR and the Unicode value of the star will insert the respective star symbols into your Google Sheets.
Example #2 – Using REPT with CONCATENATE Function
The REPT function has a maximum output length of 32,000 characters. If the total length of the repeated text exceeds it, it will be you may get an error. However, to repeat a smaller number of different symbols or text, you can use REPT with CONCATENATE as shown in the example below.
.Step 1: Suppose you have the following alphabets and numbers in Columns A and B.
Step 2: Let us print the alphabet and numbers a specific number of times using REPT and combine them to form a code of sorts. To implement this, use the following function in cell C1.
=CONCATENATE(REPT(A1,3),REPT(B1,2))
Explanation: REPT within the CONCATENATE function repeats the alphabet in cell A1 and the number in cell B1 a specific number of times. To generate a code, we combine the two using the CONCATENATE function. Press Enter.
Step 3: Now, drag the formula down from cell C1 to C6 to apply the formula to the other values and observe the result.
Example #3 – Conditional Formatting with REPT Function
REPT in Google Sheets and conditional formatting make a very good combo! We can create a custom formula by combining REPT with the IF function. This can help you add certain changes to your cell to create dynamic outputs that change based on specific criteria.
Consider a scenario where you want to color certain cells above a specific value.
Step 1: Consider the following table. Here, let us add some alphabets and numbers as shown in the previous example.
Step 2: To get the repeated values of column A in Column C, enter the following function in cell C1.
=REPT(A1,B1)
Press Enter.
Here, we have tried repeating the alphabet in cell A1, the number of times mentioned in cell B1, using the REPT in Google Sheets.
Step 3: To apply conditional formatting, highlight rows greater than 2 in the table and go to Format > Conditional Formatting.
Step 4: You get a Conditional Format rules pane on the right.
Here, select the range A1:C7. Now, go to “Format cells if.” Here, choose “Custom Formula Is.” In the box that appears, enter the following formula:
=$B1>2
For the formatting options, choose the color of your choice.
You can observe that the rows get shaded for all rows where the value in Column B is greater than 2.
Frequently Asked Questions (FAQs)
The REPT function isn’t just used for repeating strings. It can be used in several creative ways.
1. Data Visualization: You can create custom borders with cells. For instance, using =REPT(“-“, 50)
2. Pattern Generation: The function can generate repeated patterns for decorative purposes.
3. Conditional Formatting: You can use it with conditional formatting to highlight data dynamically based on certain criteria.
4. Consistency: You can use REPT to ensure data entries are of a particular format or length.
Some of the mistakes to avoid when using the REPT function include:
You should avoid excess repetition. If you exceed around 32,767 characters, you may not get the right output.
You can avoid this by limiting the repetition and testing your formulas to ensure they work as expected.
The REPT function only works with text. Hence, if you try to input a number directly, it might not work as intended. To avoid this consider using the TEXT function to convert numbers to a string.
When creating visual elements like borders, if the content exceeds the visible area of the cell, you may not get the desired output. Hence, adjust column width accordingly before using the REPT function.
If the number_of_times argument(second argument) is zero, REPT will return an empty string (“”). If it is less than zero, it returns an #VALUE! error. For example,
● =REPT(“abc”, 0) // Returns “”
● =REPT(“abc”, -2) //returns error
Download Template
This article must help understand REPT in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is REPT in Google Sheets. We learn its syntax & how to use it to repeat text with step-wise examples and a working template. You can learn more from the following articles. –
Leave a Reply