How to split text into rows
✓ Verified in LibreOffice 25.8.7.3 Google Sheets returned something else (2026-08-30)Break one delimited cell into a vertical list — turn "a,b,c" into three stacked rows for filtering, lookups, or a tidy column.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =TEXTSPLIT(A2,,",") | The empty 2nd argument means no column split; the 3rd argument is the ROW delimiter, so the pieces spill DOWN. Excel 365 / LibreOffice 24.8+. |
| Google Sheets | =TRANSPOSE(SPLIT(A2,",")) | Google Sheets has no TEXTSPLIT — executed 2026-08-30, =TEXTSPLIT("a,b,c",,",") returned #NAME? there against a, b, c in LibreOffice. SPLIT spills across columns and TRANSPOSE flips it down into rows. Documented Sheets syntax, carrying no executed result of its own. |
| LibreOffice Calc | =TEXTSPLIT(A2,,",") | Needs 24.8+. |
How it works
TEXTSPLIT takes three key arguments: the text, a column delimiter, and a row delimiter. Splitting into COLUMNS uses the 2nd argument; splitting into ROWS uses the 3rd. Leaving the column delimiter empty and passing your delimiter as the row delimiter — =TEXTSPLIT(A2,,",") — spills the pieces vertically, so "a,b,c" becomes a,b,c stacked down three cells. In Google Sheets, which lacks TEXTSPLIT, the idiom is TRANSPOSE(SPLIT(A2,",")) since SPLIT only spills horizontally. To flatten a whole delimited grid into one column, wrap it: TOCOL(TEXTSPLIT(A2,",")). Handy extras: TEXTSPLIT's later arguments let you ignore empty results and pad missing values, and you can pass an array of delimiters like {",",";"} to split on several characters at once. All of this needs dynamic arrays, and TEXTSPLIT itself — Excel 365 or LibreOffice 24.8+; in Google Sheets the SPLIT/TRANSPOSE idiom above is the equivalent.
The Google Sheets alternative
Google Sheets needs different syntax for this task. The formula below is Sheets-specific — it was executed in Google Sheets (2026-08-30) against the same sample data as the worked example, and the value beside it is what Google returned. The LibreOffice column reads n/a because the formula is outside LibreOffice’s dialect, so there is nothing of ours to report there.
| Formula | What it does | Returned by LibreOffice 25.8.7.3 | Returned by Google Sheets (executed 2026-08-30) |
|---|---|---|---|
=TRANSPOSE(SPLIT("a,b,c",",")) | SPLIT across columns, TRANSPOSE down into rows — Google Sheets has no TEXTSPLIT (over this page's literal "a,b,c", where the general form uses A2) | n/a (Sheets-only formula) | a, b, c Google Sheets alternative (executed 2026-08-30) |
Verified, not just documented
We ran =TEXTSPLIT("a,b,c",,",") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned a, b, c — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. For the worked example Google Sheets returned #NAME?, which is not what LibreOffice returned (a, b, c) — both values are shown as each engine produced them, and the disagreement itself is the finding. A further 1 row is a Google Sheets alternative: Sheets-specific syntax, executed in Google Sheets only, so the LibreOffice column reads n/a for it. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
TEXTSPLIT · TRANSPOSE · SPLIT — see full Excel, Google Sheets & LibreOffice compatibility for each.