Many a time, while working in excel you may find a necessity to repeat a particular text string, number, or symbol for a particular number of times in a cell. One way is to manually type that text string in the cell. However, excel has come up with a function called the REPT excel formula which makes this task very quickly.
In this blog, we would unlock how to use the REPT function in Excel to repeat text in cell quickly.
Here we 😎
When To Use REPT Function in Excel
The Excel REPT Function is used to repeat a particular text, number, string, or symbol in a cell a certain number of times. This function is a part of the text function group.
Syntax and Arguments
The below points explain the two arguments of REPT formula in excel.
- text – In this argument, specify the text to repeat in a cell in excel. This argument accepts anything i.e. text, number, string, or symbol.
- number_times – In this argument, specify the number of times you wish to repeat the text. It accepts a numeric value.
The arguments of the REPT function may be manually entered, or used as a cell reference. Make sure that you enter the text within double quotes.
Example of REPT Formula in Excel
In this section of the blog, we would go through some examples of excel REPT function.
Suppose, you want to repeat the letter ‘a’ in a cell A1 for 10 times. To achieve this, use the following formula in cell A1
As a result, excel would return the letter ‘a’ in cell A1 for 10 times.
Since we are manually typing the text ‘a’ inside the formula argument, it must be put within double quotes (“a”).
Similarly, you can repeat word in cell in excel for multiple times like this:
In the above example, the word “Excel” is repeated for 5 times in cell C2.
Similarly, you can also use the REPT function with number and symbols, as shown in the image below image:
In the examples learned above, there was no space characters between repeated characters and letters.
In this bonus trick section, we would learn how to repeat a particular text or number for n number of times with space in between.
There is no specific formula to achieve this. However, to achieve this simply put a space character at the end of the original text like this:
Do Not Miss These Points
- The output of the REPT function can be a maximum of 32,767 characters long. It means an output of more than 32,767 characters will return a #VALUE! error.
- If you put the number_times value as 0 (zero), then it would return a blank cell, as shown in the image below:
Thank You 🙂