REPT Function in Excel (Table of Contents)
REPT Function in Excel
If you want to fill a cell or pad values to a certain length. Or want to build histograms (a chart commonly used in financial modeling). These can be done by using REPT function by translating values directly into a certain number of characters.
What is REPT Function?
The REPT Function repeats characters a given or a specified number of times. This a built-in function in excel and falls under the Text/String function category.
It also can be used as a Worksheet function in excel. With this, the REPT function can be used as a part of a formula in a cell or worksheet.
REPT Formula in Excel
The Formula for the REPT Function in Excel is as follows:
Where the supplied arguments are:
- Text – The text you want to repeat.
- Number_times – The number of times to repeat that text.
As REPT function is a built-in function in excel and categorized under Text/String functions category, thus it’s listed under FORMULAS tab. Please follow the below steps:
- Click on FORMULAS tab.
- Select Text functions category. It will open up a drop-down list.
- Click on REPT function from the listed functions.
- After clicking on REPT function, a Function arguments box will open up.
- Pass the first argument of REPT function, which is text. The text which you want to repeat.
- The second argument is Number_times, it specifies the number of times to repeat the text. It is a positive number.
How to use REPT function in excel?
REPT Function in Excel is very simple and easy to use. Let understand the working of REPT in Excel by Some Examples.
Example #1
We have given below data:
Now we will apply the REPT function on the above data:
As per the above screenshot, the REPT function will repeat the character “_“ hyphen 5 times.
Now drag & drop this function for the below values.
The final result is:
Example #2
We also can apply the REPT function on a string as well.
We will apply the REPT function here
The results are:
Now drag & drop this function for the below values.
Example #3
To create a simple in-cell chart or column chart, we will use the REPT function. Here we will use a percentage data to create the cell chart.
Steps are as follows:
- Increase the size/height of row where you want to insert the chart so that the column bar will adjust in it.
- Insert the REPT function formula in the cell A30 and enter.
The result is:
- Now, change the orientation of text to “Rotate Text Up” as per below screenshot.
- Change the font style to PLAYBILL and alignment to bottom & center.
- Apply the same steps for the rest values.
- Finally, you will get the In-cell column chart.
With the same steps, you can also create an In cell bar chart too.
Example #4
We have given the financial data and it is needed to make all the numbers of the same length. For this, we will add Zero in the numbers in such a way so that the value of the number doesn’t change.
We want all the numbers should be of length 5. For this, we will use the REPT function:
The result is:
Drag & drop the above function for the rest values and the final results are:
Explanation:
- We will first take the length of the string.
- Then subtract the length from 5 (mentioned length which we want to make for all numbers).
- REPT function will return the number of 0’s based on the number, which is occurring after subtracting from the number 5.
For example, If the number is 91, it would return 000.
Things to Remember About REPT Function in Excel
- If the second argument number_times is Zero, then the function returns an empty string.
- If number_times is a decimal value, it will truncate to an integer. For example, if the values are 5.93, it will only consider 5.
- The result of the REPT function must be no longer than 32,767 characters. Otherwise, it will return #VALUE! error.
Recommended Articles
This has been a guide to REPT in Excel. Here we discuss the REPT Formula in Excel and how to use REPT Function in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
