How to Split Text in LibreOffice Calc (TEXTSPLIT and Text to Columns)
Split text in LibreOffice Calc using the TEXTSPLIT function in Calc 25.8+ or the Text to Columns tool in earlier versions.
Verification status: Consult the documentation and examples below. Native application testing has not been documented for this guide. How formula examples are checked
To split a cell into separate values in LibreOffice Calc, use TEXTSPLIT in LibreOffice 25.8 or later, or Data → Text to Columns in versions without that function. The formula name SPLIT used in Google Sheets is not the documented native LibreOffice Calc function.
Which method should you use?
| Method | When to use it |
|---|---|
TEXTSPLIT |
You have LibreOffice Calc 25.8 or newer and want a formula whose output updates when the source changes |
| Data → Text to Columns | You want to split a selected column without writing formulas, including on older versions |
LEFT, FIND, MID, RIGHT |
Your Calc version does not include TEXTSPLIT and you need formula-based extraction |
TEXTSPLIT syntax (LibreOffice 25.8+)
=TEXTSPLIT(Text; [Column_Delimiter]; [Row_Delimiter]; [Ignore_Empty]; [Match_Mode]; [Pad_With])
- Text — the cell reference or source text.
- Column_Delimiter — separator for splitting across columns, such as
","or"-". - Row_Delimiter — optional separator for splitting into rows.
- Ignore_Empty — optional;
TRUEignores empty values between adjacent delimiters. - Match_Mode — optional;
1means case-insensitive matching, while0is case-sensitive. - Pad_With — optional value used when rows have different numbers of elements.
In LibreOffice Calc, enter the result as an array formula when required (select an appropriate result range and use Ctrl+Shift+Enter). Unlike some other spreadsheet programs, Calc’s array-entry behavior matters here.
Example: split comma-separated text
Enter this formula:
=TEXTSPLIT("Apple,Banana,Cherry"; ",")
The result contains Apple, Banana and Cherry in separate columns of the selected array output range.
Example: split text into rows and columns
=TEXTSPLIT("A,B/C,D"; ","; "/")
This creates a 2-by-2 array: the first row contains A, B, and the second contains C, D.
Example: ignore empty fields
=TEXTSPLIT("A,,B"; ","; ; TRUE())
This skips the empty field between adjacent commas.
Split text without a formula (older LibreOffice versions)
- Put the delimited data in a column and select the cells you want to split.
- Choose Data → Text to Columns.
- Select the separator (for example, Comma, Tab or another delimiter).
- Review the preview and destination to avoid overwriting neighboring cells.
- Confirm the import or conversion.
This process is useful for one-time data cleanup. Unlike formulas, the split values will not automatically update if the source text changes.
Alternative: extract text before a hyphen
If cell A1 contains AB-123, this formula returns AB:
=LEFT(A1; FIND("-"; A1)-1)
For the part after the first hyphen, use:
=MID(A1; FIND("-"; A1)+1; LEN(A1))
These examples assume the delimiter is present in the text.
Common problems
Err:509 / unknown function
Check the function name and LibreOffice version. TEXTSPLIT requires LibreOffice 25.8 or later; SPLIT from Google Sheets examples is not the same function.
Results do not expand into adjacent cells
Confirm that you entered the formula as an array formula in an appropriately sized output range. See the array entry instructions for your installed Calc version.
Unexpected empty fields
Check adjacent delimiters and the optional Ignore_Empty parameter.
Official documentation
See the LibreOffice Calc TEXTSPLIT function reference for version requirements, examples and array entry guidance.