REPT function

Description

REPT function repeats a piece of text a specified number of times. Use REPT to fill a cell with a number of instances of a text string.

Syntax

REPT(text,number_times)

Parameters

text: is the character or string you want to repeat
number_times: is the number of times to repeat text

Remarks

1. If number_times is 0 (zero), REPT returns "" (empty text).
2. If number_times is omitted, REPT returns "" (empty text).
3. If number_times is not an integer, it is truncated.
4. The result of the REPT function cannot be longer than 32,767 characters, or REPT returns #VALUE!.

The example may be easier to understand if you copy the example data (include header) in the following table, and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.

Examples

Padding a Cell

The REPT function is sometimes used to pad a cell with characters.

The example may be easier to understand if you copy the example data (include header) in the following table, and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.

Building Text Charts

A more common use for the REPT function is to build text-based charts.

In this case, you use a numeric result in a cell as the REPT function’s number argument, and the repeated character then charts the result.

You can format the chart cells with the Webdings font or Playbill font. if the character you want to repeat is g, please use Webdings font, if the character you want to repeat is |, please use Playbill font. see example:

The example may be easier to understand if you copy the example data (include header) in the following table, and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.

Result:

Column Sparkline

You can use the REPT function to create Column Sparkline.

1. Select A2:C2, and input below formulas:

=INT(RAND()*100)

press Ctrl+Enter to end input.

2. Select D2, and input below formulas:

=REPT("|",A2/MAX($A2:$C2)*15)&CHAR(10)&REPT("|",B2/MAX($A2:$C2)*15)&CHAR(10)&REPT("|",C2/MAX($A2:$C2)*15)

3. Select D2, press Ctrl+1, click Alignment, set:
Horizontal: Center
Vertical: Bottom
Choose Wrap text
Orientation = 90 Degrees

4. Set D2 font to Playbill and font color to blue.

5. If you need to, you can adjust the column heights and widths.

Data Bar

The example may be easier to understand if you copy the example data (include header) in the following table, and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.

Result:

This example use REPT function to build Data Bar, you need to set C2:C10 font with Playbill. the formula data bar is almost the same as the Excel built-in data bar.