Contact
12 programs
Explore the site

TEXTSPLIT Function in LibreOffice Calc

Text LibreOffice Calc LibreOffice 25.8

Split LibreOffice Calc text into rows and columns with TEXTSPLIT; control consecutive separators, case matching and uneven-row padding.

Verification status: Eight ordinary INDEX-selected components, dimension or padding-check expressions were recalculated in LibreOfficeDev 26.8.0.0.alpha0 (2c87e51eeaa2b413ff4ae097b2705eea1995d8e5) using headless XLSX import and forced recalculation. Exact identities, typed results and controls were checked. LibreOffice 26.8.1.1 was source-reviewed only; stable 25.8 and GUI array entry were not executed. How formula examples are checked

Quick answer: =INDEX(TEXTSPLIT("red,blue";",");1;2) returned “blue” in the tested development build.

Availability and version history

  • Introduced: LibreOffice 25.8, explicitly stated in function-specific Help.
  • Supported in: 26.8 Help and 26.8.1.1 registration; no retirement is established.
  • Scope: Desktop Calc, English semicolon-separated formulas. Help identifies the COM.MICROSOFT.TEXTSPLIT namespace outside OpenFormula 1.3.
  • Checked: October 14, 2026. Eight listed component/dimension examples were recalculated in LibreOfficeDev 26.8.0.0.alpha0 using headless XLSX import. Stable 25.8, 26.8.1.1 and GUI array entry were not executed.

What TEXTSPLIT returns

TEXTSPLIT separates text into a rectangular text array. A column delimiter separates fields across a row; a row delimiter separates records. The implementation splits rows first, then splits each row into columns, padding shorter records to the widest result. Function Help.

Syntax and arguments

TEXTSPLIT(Text [; Column_delimiter [; Row_delimiter [; Ignore_empty [; Match_mode [; Pad_with ]]]]])

Square brackets mark optional arguments; do not type them. Text is the input string. Column_delimiter and Row_delimiter are optional text separators; Help also permits multiple delimiters, though only scalar delimiters are executed here. Ignore_empty defaults to FALSE; TRUE removes empty pieces between consecutive separators. Match_mode defaults to 0 for case-sensitive matching; 1 enables case-insensitive matching. Pad_with is optional and defaults to #N/A.

For a whole-array result, the official Help instructs array-formula entry with Ctrl+Shift+Enter. The examples below instead select components with INDEX or inspect dimensions and padding. They do not establish a GUI spill or array-entry workflow.

Worksheet setup

The examples use quoted text directly, so no input cells are required. Quoted strings in the result columns distinguish ordinary text from empty text; the surrounding quotation marks are not returned characters.

Eight reproducible examples

The native column is from LibreOfficeDev 26.8.0.0.alpha0, build 2c87e51eeaa2b413ff4ae097b2705eea1995d8e5, using headless XLSX import and forced recalculation. TRUE is a Boolean result from the padding check, not an error value. Text is shown in quotes.

Formula Independent reference LibreOfficeDev result
=INDEX(TEXTSPLIT("red,blue";",");1;2) "blue" "blue"
=INDEX(TEXTSPLIT("A|B|C";",";"|");3;1) "C" "C"
=INDEX(TEXTSPLIT("A,B|C,D";",";"|");2;2) "D" "D"
=INDEX(TEXTSPLIT("A,,B";",";"|";TRUE());1;2) "B" "B"
=INDEX(TEXTSPLIT("A,,B";",";"|";FALSE());1;2) "" ""
=INDEX(TEXTSPLIT("AxxBXXC";"xx";"|";FALSE();TRUE());1;3) "C" "C"
=INDEX(TEXTSPLIT("A,B|C";",";"|";FALSE();FALSE();"pad");2;2) "pad" "pad"
=ISNA(INDEX(TEXTSPLIT("A,B|C";",";"|");2;2)) TRUE TRUE

Practical worksheet

The text “A,B|C,D” contains two comma-separated fields in each of two pipe-separated records. =TEXTSPLIT("A,B|C,D";",";"|") gives rows A, B and C, D. Use =INDEX(TEXTSPLIT("A,B|C,D";",";"|");2;2) to check the final field D. Delimiters belong inside quotes. The semicolons between arguments are Calc syntax; they are not part of the text being split.

Advanced interpretation and checks

For “A,,B”, Ignore_empty=FALSE retains an empty text field between A and B; TRUE compresses the result to A and B. This is different from a short row that needs padding: “A,B|C” creates a missing second position in its final row, which receives #N/A or the chosen pad text. Case-insensitive matching makes “xx” split both “xx” and “XX” in the demonstrated ASCII input. The source reads custom padding as a string. Inspected implementation.

Boundaries and troubleshooting

Treat delimiters as literal text separators, not a CSV parser. The inspected splitter does not implement quoted CSV fields or escaped delimiters. Its loop also stops at the end of the input, so a trailing delimiter is not handled like the demonstrated internal consecutive delimiter; that source-only edge was not executed. Empty input is rejected by the inspected callback. Locale-sensitive Unicode case behavior, delimiter arrays and invalid inputs remain outside the native examples.

TEXTJOIN, INDEX.

Official evidence and checking scope

All eight saved formula identities and their typed results were reconciled. The stale SUM cache changed to 3 and the uncached SUM returned 7. Imported _xlfn.TEXTSPLIT formulas were saved with the documented COM.MICROSOFT.TEXTSPLIT identity. These checks establish the listed development-build components only; current-source guards and unexecuted cases remain source-derived.

The empty-text example retains its saved formula and an empty result paragraph without a serialized value-type attribute. It is recorded as an empty formula result, distinct from the true blank source cells used by the flattening examples.