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.
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
If you need to get the nth word in a text string (i.e. a sentence, phrase, or paragraph) you can so with a clever (and intimidating) formula that combines 5 Excel functions: TRIM, MID, SUBSTITUTE, REPT, and LEN.
How...
To get the last relative position (i.e. last row, last column) for text data (with or without empty cells), you can use the MATCH function.
In the example shown, the formula in E5 is:
=MATCH(REPT("z",255),...
To get the last word from a text string, you can use a formula based on the TRIM, SUBSTITUTE, RIGHT, and REPT functions.
In the example shown, the formula in C5 is:
=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT("...
To pad text to an equal length using another character, you can use a formula based on the REPT and LEN functions. In the example shown, a formula is used to append a variable number of asterisks (*) to values in column...
To pad a number with zeros, you can use a simple formula based on the TEXT function. To pad with a variable number of zeros, you can add the REPT function.
In the example show, the formula in D6 is:
=TEXT(B6,"...
To split text at an arbitrary delimiter (comma, space, pipe, etc.) you can use a formula based on the TRIM, MID, SUBSTITUTE, REPT, and LEN functions. In the example shown, the formula in C5 is:
=TRIM(MID(SUBSTITUTE($...
To extract the page, or the part of a path after the last forward slash (/), you can use a formula based on several Excel functions: TRIM, RIGHT, SUBSTITUTE, and REPT. In the example shown, the formula in C5 is:
=TRIM...
To get the last word from a text string, you can use a formula based on the TRIM, SUBSTITUTE, RIGHT, and REPT functions. In the example shown, the formula in C6 is:
=TRIM(RIGHT(SUBSTITUTE(B6," ",REPT(...
To extract a word that contains specific text,you can use a formula based on several functions, including TRIM, LEFT, SUBSTITUTE, MID, MAX, and REPT. You can use this formula to extract things like email addresses, or...
To create a simple in-cell histogram, you can use a formula based on the REPT function. This can be handy when you have straightforward data, and want to avoid the complexity of a separate chart.
=REPT(CHAR(110),C11/...
To extract lines from a multi-line cell, you can use a clever (and intimidating) formula that combines 5 Excel functions: TRIM, MID, SUBSTITUTE, REPT, and LEN.
In the example shown, the formula in D5 is:
=TRIM(MID(...
To extract words that begin with a specific character, you can use a formula based on six functions: TRIM, LEFT, SUBSTITUTE, MID, LEN, and REPT. This approach is useful if you need to extract things like a Twitter user...
To display a conditional message, without the IF function, you can use boolean logic and the REPT function. In the example shown, the formula in D5 (copied down) is:
=REPT("low",C5<100)
If the value in...
Related functions
Good links
