How To Use Excel Rept Function Examples Video

When to use Excel REPT Function

REPT function can be used when you want to repeat a specified text for a certain number of times.

If you’re wondering what can be the use of just repeating characters, check out the examples covered later in this tutorial.

What it Returns

It returns a text string that has the specified text repeat for the specified number of times.

Syntax

=REPT(text, number_times)

Input Arguments

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

Additional Notes

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, it is truncated. For example, if you use the number 10.8, it will only consider 10 and repeat the specified text 10 number of times. The result of the REPT function cannot be longer than 32,767 characters, or else it will return a #VALUE! error.

Examples of Using REPT Function in Excel

Below are some practical examples of using the REPT function in Excel.

Example 1 – Make all the Output String of the same length

While working with data in Excel (especially financial data), it is needed to make all the numbers of the same length. This can be done by adding leading zeroes in the numbers. For example, as shown below, the numbers in column B are all of the same lengths. When used in a report, these will look aligned and more professional.

Below is the formula that will do this using the REPT function. In the above function, we first calculate the length of the existing string using the LEN function. The result of the LEN function is then subtracted from 4 (which is the consistent length we want for all numbers). You can use whatever The result of the LEN function is then subtracted from 4 (which is the consistent length we want for all numbers). You can use whatever character length you want. REPT function returns the numbers of 0’s based on the existing character length of the numbers. For example, if the number is 15, it would return 00 and two zeroes are needed to make the combined string of 4 characters. Now we simply combine the REPT function with the number to get the consistent length numbers. Note that the REPT function would return the output as a string. So you may find the output aligned to the left in the cell. If needed, you can use the alignment tool to align it to the middle or to the right.

Example 2 – Creating In-cell Charts Using REPT Function

You can also use the REPT function to show some comparison right along the data right within the cell (hence called in-cell charts). Something as shown below:

This can be helpful if you’re creating an Excel dashboard and don’t want to insert a chart or use conditional formatting. Here are the steps to create these in-cell bars using REPT function:

In the cell adjacent to the score, enter the following formula =REPT("|",B2/3) Copy the formula for all the cells. Select all the cells and change the font to Stencils.

The above steps would create the in cell bars. However, these would all be in the default text font color (most likely black).

You can use conditional formatting to change the color of the bars (green if the score is greater than or equal to 35, else red). Below are the steps to use conditional formatting to show in-cell bars in specific colors.

Select the cells that have the in-cell bars/charts. Go to the Home tab. Click on the Conditional Formatting drop down. Click on New Rule. In the New Formatting Rule dialog box, select ‘Use a formula to determine which cells to format’. In the Rule field, enter the formula: =B2>=35 Click on the Format button. Change the font color to green. Close the dialog box.

You need to follow the same steps to create a new rule to highlight score less than 35 in red.

Below are some more interesting in-cell charts you can create using the REPT function.

Related Excel Functions:

Excel CONCATENATE Function. Excel LEFT Function. Excel LEN Function. Excel MID Function. Excel RIGHT Function. Excel TEXT Function. Excel TRIM Function.