SheetSense

Split text into columns with TEXTSPLIT

TEXTSPLIT cuts a text value at every delimiter and spills the pieces into neighboring cells.

fx
=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

  1. 01A2 is the text to split.
  2. 02"," is the delimiter the text is cut at.
  3. 03The pieces spill across to the right, so the cells next to the formula must be empty.

The same logic in four tools

Excel
=TEXTSPLIT(A2,",")
Google Sheets
=SPLIT(A2,",")
SQL
SELECT split_part(tags, ',', 1) AS tag1,
       split_part(tags, ',', 2) AS tag2,
       split_part(tags, ',', 3) AS tag3
FROM items;  -- PostgreSQL
Python (pandas)
parts = 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.