The formula
Excel 365Excel 365
=TEXTSPLIT(A2,",")In C2, spills: [red, large, cotton]
Google SheetsGoogle Sheets
=SPLIT(A2,",")In C3, spills: [red, large, cotton]
Any Excel: Nth item (2nd)ExcelGoogle Sheets
=TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",100)),(2-1)*100+1,100))In C4 → large
✓ Tested. All 1 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown. 2 results for formulas tagged Excel 365 / Google Sheets were checked with a reference implementation of the documented behavior, because LibreOffice can't run those functions.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Tags | ||||
| 2 | red,large,cotton | red | large | cotton | |
| 3 | red | large | cotton | ||
| 4 | large |
LibreOffice Calc can't run TEXTSPLIT or Google Sheets SPLIT, so those results were checked against the documented behavior with a reference implementation. The any-Excel fallback was verified in LibreOffice.
How it works
- A2
- The text to split.
- ","
- The delimiter. TEXTSPLIT also accepts a list like {",",";"}.
- SUBSTITUTE(…,REPT(" ",100))
- Fallback: replaces each comma with 100 spaces so each item sits in its own 100-character block.
- (2-1)*100+1
- Start of block 2. Change 2 to pick a different item.
Common errors and fixes
Why does the formula return #SPILL!?
Cells to the right aren't empty.
Google Sheets splits on spaces too?
SPLIT splits on each character of the delimiter by default. For ", " use =SPLIT(A2,", ",FALSE).
Why does the formula return #NAME??
TEXTSPLIT isn't in Excel 2021 or older. Use the fallback formula.
Does this work in Google Sheets?
Yes. Use the formulas tagged "Google Sheets" on this page; formulas tagged only "Excel" use Excel-specific syntax.