TEXTSPLIT

What it does

TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

TEXTSPLIT is the inverse of joining. It takes one string and returns a rectangular array of the pieces between delimiter occurrences.

The rule is a two-level tokenisation, applied outer-first:

1. Rows. If row_delimiter is supplied, split the text at every occurrence of it, producing r row-segments in order. If it is omitted, there is exactly one row-segment: the whole text.
2. Columns. Split each row-segment independently at every occurrence of col_delimiter, producing c_1, c_2, …, c_r fields.
3. Rectangle. The result has r rows and max(c_i) columns. Row i supplies its c_i fields left to right; the remaining max(c_i) − c_i cells of that row are filled with the padding value.

Two properties of that rule deserve to be said out loud, because they are the source of most surprises:

• Splitting is total, not selective. Every occurrence is a cut. n occurrences of a delimiter in a segment produce n+1 fields, including empty ones at the ends and between adjacent delimiters. "a,,b" split on "," is three fields, the middle one empty. ignore_empty exists precisely to opt out of that.
• The result is ragged-then-padded, not ragged. Excel's value universe has no ragged arrays; an Array is rectangular by definition (see the value universe). So a text whose rows have different field counts cannot be represented faithfully, and the padding value is how the function admits that. The default padding is #N/A, which is the honest choice: those cells contain no data, and #N/A is Excel's word for that.

Both delimiter arguments accept an array of delimiters rather than a single one, in which case any element of the array is a cut point. That makes TEXTSPLIT(t, {",",";"}) a single-pass split on either character — which is not the same as splitting on the two-character string ",;".

Arguments

TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

Two positions are routinely misunderstood.

col_delimiter is required even when you only want to split into rows. To split a newline-separated list into a column you must still supply something for position two; passing an empty string is not the same as omitting it, and the two behave differently.

ignore_empty is a field filter, not a delimiter filter, and its name invites the wrong mental model. It does not merge adjacent delimiters before splitting; it removes empty fields from the result — which also removes leading and trailing empties, and can therefore change how many columns the rectangle has.

match_mode affects only how delimiter occurrences are found. It never changes the case of the returned fields.

Try it

Quirks

Result and edge cases

The return kind is Array — genuinely, not by lifting. TEXTSPLIT is one of the functions whose kernel produces a grid, so the host-side adaptation step described in the call pipeline anchors that grid as a dynamic-array spill. Two consequences belong to the engine, not to this function, and are covered by that chapter: a blocked spill region publishes #SPILL!, and a spilled result is addressable through its anchor with the # operator.

Specific to this function:

• A one-cell result is still an array. TEXTSPLIT("abc", ",") yields a 1×1 array whose single element is "abc". The engine collapses that on publication, but a consuming function sees an array, and coercion and lifting explains why a 1×1 array argument is deliberately not flattened to a bare scalar on lift positions.
• Fields are always text. TEXTSPLIT("1,2", ",") produces the strings "1" and "2", not numbers. Wrap the result in VALUE or -- if you want numbers, and see VALUE for how locale-dependent that conversion is.
• The padding value is a value, not a format. pad_with can be text, a number, or an error; it occupies the cell exactly as given.
• Error inputs propagate ahead of any splitting.
• Array-valued text lifts. The upstream slice contract admits scalar text only, but Excel lifts TEXTSPLIT over an array-valued text, even a 1 by 1 array, and each element returns only its first piece rather than an array of arrays, which the value universe does not admit (FN-TEXTSPLIT-002).

Errors

Microsoft's page does not publish an error table for TEXTSPLIT (see Sources). What it does state is that unfilled cells of an uneven result show #N/A unless pad_with is supplied, and it recommends IFNA or pad_with to handle that. Note the framing: #N/A here is a padding value occupying data cells, not a failure of the call.

Beyond that, the errors this function can be involved in are the ordinary ones:

• an error value arriving in any argument propagates, per coercion and lifting;
• #SPILL! when the result cannot be published into the sheet — an engine outcome described in the call pipeline, not a TEXTSPLIT semantic;
• #VALUE! for arguments outside their admitted domains. Which domains, exactly, is not stated on Microsoft's page, and this Handbook has established only one: empty text, as TEXTSPLIT("",",") is #VALUE! (FN-TEXTSPLIT-001).

That last row is a real gap and is listed below rather than papered over.

How to implement

Notes for implementers

• Decide the order of ignore_empty and rectangularisation, and record it. Dropping empty fields before computing max(c_i) and dropping them after give different column counts for the same input. The documentation does not say which happens.
• Overlapping delimiters need a policy, as in the rest of this family. Splitting "aaa" on "aa" depends on whether the scan advances one character or past the match.
• An array of delimiters needs a precedence rule at a shared position. If col_delimiter is {"ab","a"} and the text contains "ab", longest-first and first-listed give different splits. Nothing documented settles this.
• A delimiter that is the empty string has no defined cut points under the model above. Treat it as an explicit case rather than letting an empty-match loop run away.
• Row and column delimiters can collide. If the same string is passed for both, the outer split consumes every occurrence and the inner split finds none. That is a consequence of outer-first ordering, and it is worth a test.

Rust code

15 textsplit_fn "TEXTSPLIT" (2, 6) [V "text", R "col_delimiter", R "row_delimiter", V "ignore_empty", V "match_mode", V "pad_with"] |s, a, sv| { textsplit(s, a, sv) }

/// An array result, or its single cell when it has one.
fn array_or_cell(site: &Site<'_>, rows: Vec<Vec<CalcValue>>) -> Step<CalcValue> {
    if rows.len() == 1 && rows[0].len() == 1 {
        return Ok(rows.into_iter().next().unwrap().into_iter().next().unwrap());
    }
    CalcArray::from_rows(rows)
        .map(CalcValue::array)
        .ok_or_else(|| site.not_modelled(None))
}

fn textsplit(site: &Site<'_>, a: &[CalcValue], sv: &mut EvalServices<'_>) -> Step<CalcValue> {
    let text = text_arg(site, a, 0)?;
    let columns = if a[1].is_missing() {
        None
    } else {
        Some(delimiters(site, a, 1, sv)?)
    };
    let rows_by = if given(a, 2) {
        Some(delimiters(site, a, 2, sv)?)
    } else {
        None
    };
    if columns.is_none() && rows_by.is_none() {
        return fail(WorksheetErrorCode::Value);
    }
    let ignore_empty = binary_flag(site, a, 3)?;
    let case_insensitive = binary_flag(site, a, 4)?;
    let pad = match a.get(5) {
        None => CalcValue::error(WorksheetErrorCode::NA),
        Some(value) if value.is_missing() => CalcValue::error(WorksheetErrorCode::NA),
        Some(value) if matches!(value.core_ref(), CoreRef::Empty) => CalcValue::number(0.0),
        Some(value) => value.clone(),
    };
    // Empty input has no text to split. Excel's typed Value2 observation
    // w111typed-00282-textsplit-arg1-empty-text is #VALUE!, not a cell of empty text (dc-zsn.7).
    if text.len_utf16_code_units() == 0 {
        return fail(WorksheetErrorCode::Value);
    }
    let mut row_texts = match &rows_by {
        Some(d) => k::split(&text, d, ignore_empty, case_insensitive),
        None => vec![text.clone()],
    };
    if row_texts.is_empty() {
        row_texts.push(ExcelText::from_utf16_code_units(Vec::new()));
    }
    let mut rows: Vec<Vec<CalcValue>> = Vec::new();
    for row in row_texts {
        let mut cells = match &columns {
            Some(d) => k::split(&row, d, ignore_empty, case_insensitive),
            None => vec![row],
        };
        if cells.is_empty() {
            cells.push(ExcelText::from_utf16_code_units(Vec::new()));
        }
        rows.push(cells.into_iter().map(CalcValue::text).collect());
    }
    let width = rows.iter().map(Vec::len).max().unwrap_or(1);
    for row in &mut rows {
        while row.len() < width {
            row.push(pad.clone());
        }
    }
    array_or_cell(site, rows)
}

/// The parts of `text` between any of `delimiters`.
pub(crate) fn split(
    text: &ExcelText,
    delimiters: &[ExcelText],
    ignore_empty: bool,
    case_insensitive: bool,
) -> Vec<ExcelText> {
    let units = text.utf16_code_units();
    if delimiters.is_empty() {
        return vec![text.clone()];
    }
    if delimiters.iter().any(|d| d.utf16_code_units().is_empty()) {
        if units.is_empty() {
            return if ignore_empty {
                Vec::new()
            } else {
                vec![ExcelText::from_utf16_code_units(Vec::new())]
            };
        }
        return units
            .iter()
            .map(|unit| ExcelText::from_utf16_code_units(vec![*unit]))
            .collect();
    }
    let mut out = Vec::new();
    let mut start = 0usize;
    let mut at = start;
    while at < units.len() {
        let found = delimiters.iter().find_map(|delimiter| {
            let needle = delimiter.utf16_code_units();
            (at + needle.len() <= units.len()
                && units_match(&units[at..at + needle.len()], needle, case_insensitive))
            .then_some(needle.len())
        });
        match found {
            Some(length) => {
                if at > start || !ignore_empty {
                    out.push(ExcelText::from_utf16_code_units(units[start..at].to_vec()));
                }
                start = at + length;
                at = start;
            }
            None => at += 1,
        }
    }
    if start < units.len() || !ignore_empty {
        out.push(ExcelText::from_utf16_code_units(units[start..].to_vec()));
    }
    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

• TEXTJOIN — the inverse operation. TEXTJOIN collapses an array into one delimited string; TEXTSPLIT expands one string into an array. They are not exact inverses: joining loses the distinction between an empty field and an absent one, so a round trip through ignore_empty is lossy.
• TEXTAFTER and TEXTBEFORE — the selective members of the same idea: one chosen occurrence rather than all of them. INDEX(TEXTSPLIT(t,d), n) and TEXTAFTER(t, d, n-1) answer related questions differently.
• ARRAYTOTEXT — shares an implementation module upstream and is the usual way to make a TEXTSPLIT result inspectable as a single string.
• SUBSTITUTE plus FIND plus MID was the classic pre-dynamic-array splitting idiom, along with the Text to Columns command. Neither is superseded in the formal sense; both remain available.
• Confusable with SPLIT from VBA and with Power Query's split-column step. Neither shares this function's padding or ignore_empty semantics.

Named variants

No named variants are recorded yet.