Spreadsheet Practice

How to split comma-separated values into rows in Excel

Excel formula

=TEXTSPLIT(A2,,", ")

Use TEXTSPLIT with the delimiter as its third argument, the row delimiter: =TEXTSPLIT(A2,,", ").

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

See it work

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

How it works

TEXTSPLIT's second argument splits into columns and its third into rows. Leaving the second empty and giving the third ", " puts each name on its own row. Including the space in the delimiter means no name starts with a space.

Google Sheets has SPLIT, which splits across, so TRANSPOSE turns the row into a column. FALSE makes SPLIT treat ", " as one delimiter instead of splitting at every space too.

=TEXTSPLIT(A2,,", ")

Result{5 rows × 1 col} "Ana Ruiz", "Raj Patel", "Lee Chen", "Kim Ortiz", …

  1. A2"Ana Ruiz, Raj Patel, Lee Chen, Kim Ortiz, Mo Diaz"

    The value in A2.

  2. TEXTSPLIT(A2,,", "){5 rows × 1 col} "Ana Ruiz", "Raj Patel", "Lee Chen", "Kim Ortiz", …

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

    • textA2"Ana Ruiz, Raj Patel, Lee Chen, Kim Ortiz, Mo Diaz"
    • col_delimiter(omitted)
    • row_delimiter", "

In Google Sheets

Google Sheets needs a different formula: =TRANSPOSE(SPLIT(A2,", ",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
=TEXTSPLIT(A2,", ")spills 1 × 5Different resultWith the delimiter as the second argument, the names go across one row instead. One row of five names.

Common mistakes

  • If some entries are separated by "," and others by ", ", split at the comma and TRIM the pieces: =TRIM(TEXTSPLIT(A2,,",")).
  • TEXTSPLIT needs Excel 2024 or Microsoft 365. In older versions, use Data > Text to Columns and then Paste Special > Transpose.

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 rows

In C2, split the comma-separated names in A2 into a column, one name per row.

Questions

How do I split a whole column of lists into one list?

Join the column first, then split: =TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:A6),,", ").

How do I split by more than one delimiter?

Give TEXTSPLIT an array of delimiters: =TEXTSPLIT(A2,,{",",";"}).

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.