Split text into columns with TEXTSPLIT
TEXTSPLIT cuts a text value at every delimiter and spills the pieces into neighboring cells.
=TEXTSPLIT(A2,",")The sheet: Column A holds tags typed as red,blue,green. You want each tag in its own cell to the right.
How it works, step by step
- 01A2 is the text to split.
- 02"," is the delimiter the text is cut at.
- 03The pieces spill across to the right, so the cells next to the formula must be empty.
The same logic in four tools
=TEXTSPLIT(A2,",")=SPLIT(A2,",")SELECT split_part(tags, ',', 1) AS tag1,
split_part(tags, ',', 2) AS tag2,
split_part(tags, ',', 3) AS tag3
FROM items; -- PostgreSQLparts = df["tags"].str.split(",", expand=True)Mistakes that break it
- !TEXTSPLIT is only in Microsoft 365 and Excel for the web. Older versions use Text to Columns. Google Sheets uses SPLIT instead.
- !If a neighboring cell is not empty the formula returns #SPILL!.
- !Spaces after commas stay in the pieces. Wrap with TRIM, for example =TRIM(TEXTSPLIT(A2,",")).
- !Rows with fewer pieces than others leave blank or #N/A cells, depending on the tool.
Common questions
- How do I split into rows instead of columns?
- In Excel, pass an empty column delimiter and the row delimiter: =TEXTSPLIT(A2,,","). In Sheets use TRANSPOSE(SPLIT(A2,",")).
- How do I split a full name?
- See the split full name entry, which uses TEXTBEFORE and TEXTAFTER.
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.