Spreadsheet Practice

How to split comma-separated values into one column in Excel

Excel formula

=TEXTSPLIT(TEXTJOIN(", ",TRUE,B2:B6),,", ")

Join all the cells, then split the result into rows: =TEXTSPLIT(TEXTJOIN(", ",TRUE,B2:B6),,", ").

Result on the example
a 9-row list that spills from D2 in D2
Works in
Excel 2024 and Microsoft 365
Google Sheets
Different formula, see below

See it work

The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.

How it works

TEXTSPLIT works on one piece of text, so first TEXTJOIN glues every cell into a single list, skipping the blank one. Then TEXTSPLIT splits it with ", " as the row delimiter (the third argument), so each name spills into its own row.

In Google Sheets, SPLIT always returns a row, and TRANSPOSE turns it into a column.

=TEXTSPLIT(TEXTJOIN(", ",TRUE,B2:B6),,", ")

Result{9 rows × 1 col} "Aiko", "Ben", "Chidi", "Dana", …

  1. B2:B65 rows × 1 col: "Aiko, Ben, Chidi", "Dana", (empty), "Eli, Fay", …

    A range: 5 cells in one column.

  2. TEXTJOIN(", ",TRUE,B2:B6)"Aiko, Ben, Chidi, Dana, Eli, Fay, Gus, Hana, Ivo"

    TEXTJOIN: Joins text with a delimiter between items.

    • delimiter", "
    • ignore_emptyTRUE
    • text1B2:B65 rows × 1 col: "Aiko, Ben, Chidi", "Dana", (empty), "Eli, Fay", …
  3. TEXTSPLIT(TEXTJOIN(", ",TRUE,B2:B6),,", "){9 rows × 1 col} "Aiko", "Ben", "Chidi", "Dana", …

    TEXTSPLIT: Splits text across columns, and rows, at the delimiters you give.

    • textTEXTJOIN(", ",TRUE,B2:B6)"Aiko, Ben, Chidi, Dana, Eli, Fay, Gus, Hana, Ivo"
    • col_delimiter(omitted)
    • row_delimiter", "

In Google Sheets

Google Sheets needs a different formula: =TRANSPOSE(SPLIT(TEXTJOIN(", ",TRUE,B2:B6),", ",FALSE)). On the same data it gives the same result. Here it is running in Google Sheets mode.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=TRIM(TEXTSPLIT(TEXTJOIN(",",TRUE,B2:B6),,","))spills 9 × 1Splits on the comma alone and trims each name, for lists typed with and without spaces after the commas.

Common mistakes

  • TEXTJOIN can return at most 32,767 characters. For very long lists, split in batches.
  • Putting ", " in the second argument instead of the third spills the names across a row.
  • The same name in two teams appears twice. Wrap the formula in UNIQUE for a distinct list.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Split comma-separated values into one column

In D2, list every member from B2:B6 in one column, one name per row.

Questions

How do I get a sorted list without repeats?

Wrap it: =SORT(UNIQUE(TEXTSPLIT(TEXTJOIN(", ",TRUE,B2:B6),,", "))).

How do I count the members?

Count the list: =COUNTA(TEXTSPLIT(TEXTJOIN(", ",TRUE,B2:B6),,", ")).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.