Excel REPT Function
Summary
The Excel REPT function repeats characters a given number of times. For example, =REPT("x",5) returns "xxxxx".
Purpose
Repeat text as specified
Return value
The repeated text.
Syntax
=REPT (text, number_times)
Arguments
- text - The text to repeat.
- number_times - The number of times to repeat text.
Version
Usage notes
The REPT function repeats characters a specified number of times. For example, to repeat "x" five times, you can use the following formula:
=REPT("x",5) // returns "xxxxx"
Inputs to REPT can be variable. In the example shown, REPT is configured to repeat the string in column B using the count in column C. The formula in D5 is:
=REPT(B5,C5) // returns "-----"
The REPT function can be used in clever ways in more advanced formulas. See links below for specific examples.
Notes
- Use the REPT function to repeat text a given number of times. This can be useful if you want to fill a cell, or pad values to a certain length.
- It's possible to build a basic histogram chart using REPT, by translating values directly into a certain number of (repeated) characters.
REPT formula examples
This formula uses the MATCH function in approximate match mode to locate the last text value in a range. Approximate match enabled by setting by the 3rd argument in MATCH to 1, or omitting this argument, which defaults...
This formula is an interesting example of a "brute force" approach that takes advantage of the fact that TRIM will remove any number of leading spaces.
Working from the inside out, we use the SUBSTITUTE function to...
This formula concatenates the original value in column B to a string of asterisks (*) assembled with the REPT function so that the final result is always 12 characters:
REPT("*",12-LEN(B5))
Inside the REPT...
This formula uses boolean logic to output a conditional message. If the value in column C is less than 100, the formula returns "low". If not, the formula returns an empty string ("").
Boolean logic is a technique of...
The REPT function simply repeats values. For example, this formula outputs 10 asterisks:
=REPT("*",10) // outputs **********
You can use REPT to repeat any character(s) you like. In this example, we use...
The TEXT function can apply number formats of any kind to numbers. It is most often used when you want to maintain number formatting for a number when concatenating that number with other text.
In this case, the TEXT...
This formula takes advantage of the fact that TRIM will remove any number of leading spaces. We look for line breaks and "flood" the text with spaces where we find one. Then we come back and grab text from the right....
At the core, this formula takes a text string with spaces, and "floods" it with additional spaces by replacing each space with a number of spaces using SUBSTITUTE and REPT. The number of spaces used is based on the...
The gist: this formula "floods" the space between words in a text string with a large number of spaces, finds and extracts the substring of interest, and uses the TRIM function to clean up the mess.
Working from the...
At the core, this formula replaces all forward slashes (/) with 100 spaces, then extracts 100 characters from the RIGHT and cleans this up with the TRIM function.
The replacement is done with SUBSTITUTE and REPT here...
At the core, this formula looks for a line delimiter ("delim") and replaces it with a large number of spaces using the SUBSTITUTE and REPT functions.
Note: In older versions of Excel on a Mac, use CHAR(13) instead of...
Starting from the inside out, the MID function is used to extract all text after "@":
MID(B5,FIND("@",B5),LEN(B5))
The FIND function provides the starting point, and for total characters to extract, we...
The gist of this formula is to replace a given delimiter with a large number of spaces using SUBSTITUTE and REPT, then use the MID function to extract text related to the "nth occurrence" and the TRIM function to get...
Related videos
In this video, we look at how to use the REPT function, a special function for repeating text values with a numeric counter.
Related functions
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.
