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

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

**Excel:** `=TEXTSPLIT(TEXTJOIN(", ",TRUE,B2:B6),,", ")` (Excel 2024 and Microsoft 365)

**Google Sheets:** `=TRANSPOSE(SPLIT(TEXTJOIN(", ",TRUE,B2:B6),", ",FALSE))`

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Team | Members |  | All members |
| Design | Aiko, Ben, Chidi |  |  |
| Sales | Dana |  |  |
| Support |  |  |  |
| IT | Eli, Fay |  |  |
| Finance | Gus, Hana, Ivo |  |  |

Formula in D2: `=TEXTSPLIT(TEXTJOIN(", ",TRUE,B2:B6),,", ")`

Result (spills 9 × 1): Aiko; Ben; Chidi; Dana; Eli; Fay; Gus; Hana; Ivo

## 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.

## Other ways

- `=TRIM(TEXTSPLIT(TEXTJOIN(",",TRUE,B2:B6),,","))`: Splits 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.

## 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),,", "))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/split-comma-separated-values-into-one-column