REPT

What it does

REPT(text, number_times)

REPT(text, number_times) returns text concatenated with itself number_times times:

REPT(t, n)  =  t · t · … · t     (n copies)
REPT(t, 0)  =  ""

number_times is truncated to an integer before use — Microsoft states this explicitly — so REPT("ab", 2.9) is REPT("ab", 2).

The interesting property of this function is not the concatenation. It is that REPT is the cheapest way in the whole worksheet language to manufacture a string longer than Excel will hold, which makes it the function against which the text cap is most often observed. The Handbook's value-universe chapter records that observation: producing an over-cap string in a formula yields #VALUE!, and REPT is the function it was observed with.

The result length is LEN(text) × TRUNC(number_times) code units, and the documented ceiling is 32,767.

Arguments

number_times is a numeric slot and takes ordinary to-number coercion; see Coercion and lifting. text may be any value that converts to text, so REPT(0, 3) is a to-text question before it is a REPT question.

Try it

Quirks

Result and edge cases

Returns Text.

Documented:

• number_times of 0 returns the empty string "".
• Non-integer number_times is truncated.
• A result longer than 32,767 characters returns #VALUE!.

Not documented, and therefore not stated as fact here: what a negative number_times does. Microsoft's page describes the argument as "a positive number" and states the zero case, but does not state the negative case. #VALUE! is the obvious expectation and the obvious thing to probe.

Also undocumented: what REPT("", n) does for large n — the result is empty regardless, but whether the cap check runs before or after the empty-string shortcut decides whether REPT("", 10^9) is instant or is an error.

Errors

Error values in either argument propagate. Whether a negative number_times produces #VALUE! or #NUM! is unverified — see above.

The over-cap rule deserves emphasis because the same cap behaves differently on a different path. The Handbook's value-universe chapter records that assigning an over-cap string through the automation interface truncates silently, while producing one in a formula errors. Same limit, two enforcement behaviours, decided by how the string arrives. REPT is on the erroring side.

How to implement

Notes for implementers

The cap check must precede the allocation, not follow it. REPT("x", 10^9) is a documented #VALUE!, and an implementation that discovers this by building the string first has a denial-of-service surface where Excel has an error value. Compute LEN(text) × TRUNC(number_times), compare against 32,767, and only then allocate.

That multiplication itself needs care: number_times arrives as a double and can be astronomically large, so the length computation has to be done in a form that cannot overflow before the comparison happens.

Truncation is toward zero, which is what makes the negative case a real question rather than a formality — TRUNC(-0.5) is 0, so a naive truncate-then-loop implementation would return the empty string for REPT("a", -0.5) and something else for REPT("a", -1). Whatever Excel does, an implementation should not arrive at two different answers for those two inputs by accident.

Counting is in UTF-16 code units against the cap, consistent with LEN.

Rust code

18 rept_fn "REPT" (2, 2) [V "text", V "number_times"] |s, a, sv| {
    let text = text_arg(s, a, 0)?;
    let n = num(s, a, 1)?;
    out_text(k::rept(&text, n))
}

pub(crate) fn rept(text: &ExcelText, count: f64) -> Result<ExcelText, WorksheetErrorCode> {
    let count = count.trunc();
    if count < 0.0 {
        return Err(WorksheetErrorCode::Value);
    }
    let units = text.utf16_code_units();
    if count > EXCEL_TEXT_MAX_UTF16_CODE_UNITS as f64
        || units.len().saturating_mul(count as usize) > EXCEL_TEXT_MAX_UTF16_CODE_UNITS
    {
        return Err(WorksheetErrorCode::Value);
    }
    let count = count as usize;
    let mut out = Vec::with_capacity(units.len().saturating_mul(count));
    for _ in 0..count {
        out.extend_from_slice(units);
    }
    Ok(ExcelText::from_utf16_code_units(out))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• CONCAT / TEXTJOIN / the & operator are the general concatenation route; REPT is the special case where all the pieces are the same and the count is computed.
• TEXTJOIN shares the 32,767-character result ceiling and the #VALUE! on exceeding it, and is the other text function most likely to hit it.
• LEN is the natural companion, both for predicting the cap and for the classic in-cell bar-chart idiom REPT("|", value).
• There is no compatibility-replacement pair here: REPT is neither superseded nor a retained legacy name.
• Readers occasionally confuse REPT with SUBSTITUTE when trying to pad strings. Padding is REPT plus LEN; SUBSTITUTE has nothing to do with it.

Named variants

No named variants are recorded yet.