March 8, 2021

REPT function in Excel

In this lesson we will learn about the REPT function in Excel. The REPT function is used when you want to repeat a specified text for a certain number of times. It returns a string that has the specified text repeat for the specified number of times.

=REPT(text, number_times)
  • text – the text to be repeated. You can specify this with the double quotes, or have it in a cell and use the cell reference.
  • number_times – the number of times text should be repeated. You can specify the number by manually entering it in the formula, or have the number in a cell and use the cell reference instead.

Let’s understand with a simple example. In the below table, we have a list of bank account numbers for certain customers in column A. 

We have used the below formula to make all the account numbers of the same length, that is, to make each account number is of 16 digits by adding leading zeros. This formula has been added in cell B2 and dragged for the following cells in column B.

=REPT("0",16-LEN(A2))&A2

Here, we first calculate the length of the existing account number in each cell of column A ,using the LEN function. The result of the LEN function is then subtracted from 16 (which is the consistent length we want for all account numbers) to get the second argument of the REPT function. In place of 16, you can use whatever character length you want.

Now the REPT function has 2 arguments. First one is the text “0” which is to repeat and the second one is the number of times to be repeated.

Now we simply combine the REPT function with the number to get the consistent length numbers.

Notes :

  • The REPT function returns the output as a string.
  • If the second argument (number of times to repeat) is 0, it returns blank.
  • If the second argument (number of times to repeat) is not an integer value, it is truncated. For example, if you use the number 20.7, it will only consider 20 and repeat the specified text 20 times.
  • The result of the REPT function cannot be longer than 32,767 characters else it will return a #VALUE! Error.

You can also check here to learn the REPT function in Google Sheet.

Leave a Reply

Your email address will not be published. Required fields are marked *