Google Sheets SPLIT formula: split text into columns or rows by a comma, space or any character

Say which cell holds the text and what separates the parts. Copy the formula. You can split one cell into columns or into rows, split a whole column at once, or take only the 2nd or the last part. It runs in your browser and nothing you type here is sent anywhere. Every formula below was run in a real Google Sheet on 3 November 2026 (parts with spaces, empty parts, a trailing comma, upper and lower case, numbers with leading zeros, dates, empty cells) once in a US-locale spreadsheet and once in a German-locale one with semicolons: all checks matched.

Your formula

How to use it

Paste the formula into one empty cell. The parts spill to the right (columns) or down (rows), so the cells next to it must be empty, or Sheets shows a #REF! error. The whole-column choices need the cells below and to the right to be empty too.

Which one to pick

You pick	The formula uses
Into columns	`SPLIT(A2, ",")`
Into rows	`TRANSPOSE(SPLIT(A2, ","))`
Whole column	`ARRAYFORMULA(IFERROR(SPLIT(A2:A, ","), ""))`
Nth part	`INDEX(SPLIT(A2, ","), 1, 2)`
Last part	`INDEX(SPLIT(A2, ","), 1, COLUMNS(SPLIT(A2, ",")))`
Spaces cut	`ARRAYFORMULA(TRIM(SPLIT(A2, ",")))`

The catches (found by testing in a real Sheet)

Every character of the separator splits, by default. SPLIT(A2, ", ") on a,b c gave three parts: a, b, c, because the comma and the space each split. Tick "the separator is the whole text" to split only on a comma followed by a space: a,b c then stays in one piece.
Spaces stay in the parts. a, b, c split on a comma gave a, b, c with a space in front of the last two. Tick "cut spaces" to wrap the result in TRIM.
Empty parts are dropped. a,,c gave two parts. Tick "keep empty parts" and it gave three, with an empty one in the middle. A comma at the very end is dropped either way: a,b, gave two parts even with empty parts kept. A comma at the start is kept when you ask for empty parts.
Numbers and dates change. 007,8 came back as the numbers 7 and 8: the leading zeros are gone. 1/2 and 3-4 came back as dates (shown as numbers like 46054 until you format the cells). In the German-locale sheet 1.5 became a date too. If you need the text as it was, format the output cells as plain text first, or take parts with REGEXEXTRACT instead.
Upper and lower case count. Splitting aXbxc on x gave aXb and c: the capital X did not split.
An empty cell gives #VALUE!. SPLIT refuses an empty cell. In a whole column that means an error for every empty row, so the whole-column choices wrap the formula in IFERROR and leave the empty rows blank. Fill in the "if the cell is empty" box to show your own text instead.
A part that does not exist gives #REF!. Asking for part 5 of a,b,c returned #REF!. The Nth and last choices accept your own text for that case. For a whole column a missing part is just an empty cell.
The last part. It uses COLUMNS to count the parts, so a trailing comma does not give an empty answer: a,b, returned b.
Quotes, tabs and new lines. A double quote as the separator works (the builder doubles it in the formula); \t and \n become CHAR(9) and CHAR(10). Stars, dots and bars split normally, they are not special here.
Decimal comma. In a German-locale sheet the same formula needs semicolons between arguments; pick the semicolon separator.

No code: KeepSorted

The add-on sorts the original table in place without code: no second copy, so formatting, checkboxes and notes move with their rows and people keep editing the same table. Select the range, pick the sort columns, Save. Free: one area per tab, up to 2 sort columns, no trial clock. Pro ($5/month or $39/year): up to 5 sort columns, custom orders like High > Medium > Low, ticked checkbox rows to the bottom, stacked tables sorted separately, and sorting after form responses, other editors' changes and an hourly check.

KeepSorted is built and in testing. It is not in the Google Workspace Marketplace yet. Leave your email and we send one message when you can install it.

You're on the list. We'll email you when KeepSorted is ready.

The generators and the formulas stay free either way.

More KeepSorted tools and guides

Sources

Google Docs Editors Help: SPLIT (delimiter, split by each, remove empty text)
Google Docs Editors function list (SPLIT, TRANSPOSE, INDEX, COLUMNS, TRIM, IFERROR, ARRAYFORMULA)