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
- 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
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 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 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 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 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 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 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/...
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 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 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 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,"...
Related functions
Good links
