Excel Functions Tutorials
- Excel Formulas Cheatsheet
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- Date and Time Function in Excel
- Statistical Function in Excel
Excel REPT Function (Table of Contents)
REPT Function in Excel
It is a built-in function in MS Excel. It falls under the category of string or text functions in Excel. REPT Function in Excel is used to repeat the text a specified number of times.
REPT Formula in Excel
Below is the REPT Formula in Excel.
The REPT Function has two arguments out of which both are required. Where,
- text = It is a required parameter. It is the text to be repeated.
- number_of_times = It is also a required parameter. It is the number of times the text in the parameter-1 is to be repeated. It must be a positive number.
How to use REPT Function in Excel?
The REPT Function is a Worksheet (WS) function. As a WS function, REPT Function can be entered as a part of the formula in a cell of a worksheet. Refer to a couple of examples given below to know more.
REPT Function in Excel Worksheet
Let’s look at the REPT in Excel examples given below. Each example covers a different use case implemented using REPT function in Excel.
Excel REPT Function Example #1 – Repeat a set of characters
In above REPT Function example, REPT(“\n”,3) “\n” is said to be repeated 3 times. As a result, cell B2 contains “\n\n\n\”.
REPT Function Example #2 – Repeat a character
In above example, REPT (“-”,10) “-” is said to be repeated 10 times. As a result, cell C1 contains “———-”.
REPT Function Example #3 – Equalize the length of the numbers
In above example, column C contains a series of numbers from C3 to C7. The number of digits each cell contains is different. So, the use case is to make all the numbers of same length by adding zeros (0) in such a way that the value of the number doesn’t change but all the numbers become of the same length. Here, the length defined is 4. i.e. all the numbers should be of length 4. The formatted numbers are present in column D starting from D3 to D7. So, formatted number of C3 is present in D3, C4 in D4, C5 in D5, C6 in D6, C7 in D7, and so on. Each cell from D3 to D7 has a REPT Function applied on it.
Here, REPT function in excel is used to add leading or trailing zeros. Let’s analyze the REPT function in excel written in the formula bar.
As shown in the above REPT formula in Excel, “0” represents a digit / text to be repeated and the second parameter is the frequency with which zero is it to be repeated. Here, length of the number in C3 cell is deducted from number 4 which will give us the number of zeros to be repeated so to format the number in 4-digit format. E. g: If the length of the number present in C3 is 2, then 4-2=2 which is evaluated to: – REPT (“0”,2) meaning repeat 0 two times. Further, the resultant number is ended with the original value which here, is 12 and the result is 0012.
REPT Function Example #4– Create in-cell charts
In above example, column E contains a series of marks obtained out of 100 from E3 to E7. The marks obtained are to be represented in a pictorial format without using default Charts widget in Excel. This can be accomplished by using the REPT in Excel, as explained below.
Here the REPT formula in Excel used is =REPT(CHAR(110),E3*0.05).
All characters have ASCII code which is understood by the computer. Small case ‘n’ has code 110. So, =CHAR (110) in Excel returns ‘n’. When Wingdings font is applied on the same, it becomes a block displayed as – n.
Now, we have got the character to be repeated. Now, we must figure out how many times it is to be repeated. So, the REPT function in Excel applied to calculate the number of blocks is: – E3*0.05 meaning one block would represent 1/5th part of the value. So, the number of blocks for marks 70 is shown as 3 repeated blocks in the result cell F3. Similar formula is repeated on the remaining values i.e. from E4 to E7 resulting into 4, 1, 2 and 5 blocks for marks 80, 20, 40 and 100 shown in the result cells F4, F5, F6, F7 respectively.
Things to Remember about REPT Function in Excel
- The 2nd parameter of the REPT function must be a positive number.
- If it is 0 (zero), the REPT function returns “” i.e. an empty text.
- If it is not an integer, it is truncated.
- The result of the REPT function cannot be longer than 32,767 characters, else the REPT in Excel returns #VALUE! indicating an error with the value.
You can download this REPT Function in Excel template here – REPT Function Excel Template
This has been a guide to REPT Function in Excel. Here we discuss the REPT Formula in excel and how to use REPT function in excel along with REPT function examples and downloadable excel templates. You may also look at these useful functions in excel –