How to use the REPT function in Excel

The Excel REPT function is a simple function, that prints a given text to given times. In this article, we will explore how REPT function works, how REPT functions can be used, and where REPT function is used best, what tricks can be done using this function. There’s so much, just keep reading.
0012
REPT Function Syntax

=REPT(“text”,number)

Text: The characters/s you want to repeat.

Number: The number of times you want to repeat a given text.

For example, if I want to print “HA” three times, I will write REPT formula as shown below.

=REPT(“HA”,3)

The formula above will return “HAHAHA” as a result. Now at this point, The REPT function doesn’t sound very useful. But wait, it is more useful than it sounds. See below use cases of Excel REPT function.
Excel REPT Function for In Cell Bar Chart
I know that, you can create in cell bar charts using conditional formatting options. But they have their own limitations. Like you cant change color based on condition.
So for any given reason, if you want to create a progress bar chart in a cell, follow below example.

In-cell bar chart using REPT function

I have this data, that shows the target and achievement of the writers on exceltip.com. In column D, we will use REPT function to create a bar that shows the progress of writers. The progress bar is of % achievement.
0014

    1. Write this REPT formula in Cell D2. The character in double quotes is column. It’s with back slash “\”. The character code is 124. If you don’t find it, write =CHAR(124). You’ll get it.

=REPT("|",C5/B5*100)

    1. Now change font of cell D2 as “Stencil” from home tab. Adjust font size if it doesn’t look solid.

0015
And now you have a bar chart that shows the progress of writers in one cell. All this you’ve done using REPT function of Excel.
0017
Now if you want to color these bars on some conditions, you can use conditional formatting too. For example here, I have I want to bar to be red if it’s less then 50%, else Green. So I need to apply conditional formatting based on formula (if<50% and if>=50%).
0018
Finally I will have this bar chart created using REPT function.
0019

Note: If you supply a fraction number to REPT function, it will be truncated. For example if you supply 50.92, it will make it 50. If you want to round it to closest value, Use ROUND function.

Repeat Formula in Excel to Print on a Condition
You can use REPT function to print something based on a condition, similar to Excel IF Function. Let’s see this example.
In this example, I have same data as above, but instead of progress bar, I want to print “Yes” if a writer has achieved 50% of his target.
0020
So, in cell I5 write this Repeat Formula, and copy down the cells.

=REPT("Yes",(H5/G5)>50%)

This will give us the desired result as shown below.
0021
How It Works?

Actually, This Repeat function converts boolean values into number. True is 1 and False is 0. So when if given condition is True, it prints the given string one time. If false it prints the given string zero times, means nothing is printed. The above function is equivalent to =IF((H5/G5)>50%,”Yes”,””).

Note: It is kind of obvious but still, If you supply 0 as repetition number of text,nothing will be printed.
REPT Function to Maintain Text Length
For this example, let’s assume that we get name of candidates in cell. For any purpose, we want text to be length of at least 20. If any name is less then 20 characters, we will ad remaining letters as -- after the name.
If you know about LEN function of excel, you’ve must figured out to how to do this. I am explaining it here, anyway.
0022
Here I have some names. And I want to add hyphen (-) after the name and repeat it to remaining letters.
The formula is simple, write the name first, then count characters in name and subtract it from 10.

Write this REPT Formula in cell B11, and copy in below cells.

=A11&REPT("-",10-LEN(A11))

This will give us the desired result.
0023
So, yeah guys, this is the REPT function that is also called Repeat function in excel. This is simple. Just repeats a string given times. But in smart hands this can be used as very powerful function. This can be used to create really impressive dashboards.

If you know any amazing use of this function, or of any other excel function in excel, let us know in the comments section below. Or if you have any doubt about this function, you can ask that too.

Articles other visitors visited:

Excel Sparklines : The Tiny Charts in Cell

The IF function

Conditional Formatting with formula

How to Use REPLACE function in Excel

How to Use SUBSTITUTE function in Excel

 

Popular Articles

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

 

Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube