How to Split Text Into Columns in Excel and Google Sheets
An export arrives with "Lopez, Maria" in one cell, or a product code like SKU-1042-BLU-L that you need as four separate fields. Splitting text is the reverse of combining it, and spreadsheets give you two approaches: a one-time menu command that rewrites the data, or a formula that keeps updating when the source changes. Here is how both work, with the results each formula returns in Excel.
1. Option 1: Text to Columns (a one-time split)
Microsoft's steps for the Convert Text to Columns Wizard:
- Select the cell or column with the text.
- Go to Data > Text to Columns.
- Choose Delimited, then Next.
- Pick the delimiters your data uses, such as Comma and Space. The Data preview window shows the result.
- Select Next, choose a Destination, and select Finish.
The Destination step deserves a pause. The split pieces fill columns to the right, so pick a starting cell with enough empty columns beside it, or work on a copy.
Google Sheets has the same tool at Data > Split text to columns. Google's help page uses the "Last name, First name" case as its example and lets you choose the separator from a drop-down, or leave it on Detect automatically.
The menu approach is quick, but the result is static. If the source cell changes next week, the split columns do not.
2. Option 2: TEXTSPLIT (a split that updates)
In current versions of Excel, TEXTSPLIT does the same job as a formula. Microsoft describes it as working like the Text to Columns wizard, "but in formula form," and as the inverse of TEXTJOIN. The basic shape is TEXTSPLIT(text, col_delimiter).
With "Dakota Lennon Sanchez" in A3:
=TEXTSPLIT(A3," ")
The result spills across three cells: Dakota, Lennon, Sanchez. For the name in last-name-first order, include the space in the delimiter:
=TEXTSPLIT(A2,", ")
With "Lopez, Maria" in A2, this returns Lopez and Maria with no leftover comma or space. If you split on a space alone, the first piece comes back as "Lopez," with the comma still attached.
Product codes work the same way. =TEXTSPLIT(A5,"-") on SKU-1042-BLU-L returns SKU, 1042, BLU and L in four cells.
3. Handling empty pieces and several delimiters
When two delimiters sit next to each other, TEXTSPLIT creates an empty cell by default. Microsoft lists an optional fourth argument, ignore_empty, which defaults to FALSE. With "red,,green,blue" in A7:
=TEXTSPLIT(A7,",")
=TEXTSPLIT(A7,",",,TRUE)
The first returns red, an empty cell, green and blue. The second returns just red, green, blue. Note the two commas before TRUE: they skip the optional row delimiter.
To split on more than one character, Microsoft's remarks say to use an array constant in curly braces, such as {",","."}. TEXTSPLIT can also split down rows. Microsoft's own example, =TEXTSPLIT(A2,",",";") on "1,2,3;4,5,6", returns a grid of two rows and three columns.
4. Splits give you text, not numbers
Every piece TEXTSPLIT returns is text, even when it looks like a number. That is helpful for ZIP codes: splitting "Iselin, NJ, 08830" keeps 08830 with its leading zero. It is a problem if you want to add the pieces:
=SUM(TEXTSPLIT("1,2,3",","))
=SUM(--TEXTSPLIT("1,2,3",","))
The first returns 0, because SUM ignores text. The second returns 6. The double minus converts each piece to a number before SUM sees it.
5. Getting just one piece: TEXTBEFORE and TEXTAFTER
Often you only need the first name or the last word. Two related functions handle that without spilling extra cells:
=TEXTBEFORE(A3," ")
=TEXTAFTER(A3," ",-1)
These return Dakota and Sanchez. In TEXTAFTER, the -1 means "count from the end," so you get the text after the last space no matter how many middle names there are.
Microsoft's TEXTBEFORE page warns that it returns #N/A when the delimiter is not found. A one-word name like "Socrates" has no space, so =TEXTBEFORE(A8," ") returns #N/A. Microsoft's examples show the fix: the match_end argument, set to 1, treats the end of the text as a delimiter. =TEXTBEFORE(A8," ",,,1) returns Socrates.
6. When the result says #SPILL!
Because TEXTSPLIT returns several cells, it needs empty room to spill into. If any of those cells already holds something, even a single typed letter, the formula shows #SPILL!. Microsoft's guidance: select the formula cell to see a dashed border around the intended range, use Select Obstructing Cells from the error alert, then clear or move what is in the way. Microsoft also notes that spilled formulas are not supported inside Excel tables, and cannot spill into merged cells.
7. A Google Sheets difference worth knowing
Google's SPLIT function looks similar, but its documentation describes an important default: each character in the delimiter is treated separately. A delimiter of ", " splits at every comma and every space, so a city like "New York" would break apart too. Google's page explains that setting the third argument, split_by_each, to FALSE turns this off. Its fourth argument, remove_empty_text, defaults to TRUE, the opposite of Excel's TEXTSPLIT.
8. Key takeaways
- Data > Text to Columns (Excel) or Data > Split text to columns (Sheets) splits once and does not update.
- TEXTSPLIT splits with a formula that updates; include the space in ", " to avoid leftover characters.
- TEXTSPLIT results are text, so wrap them in a double minus before doing math.
- TEXTBEFORE and TEXTAFTER pull a single piece; -1 counts from the end.
- #SPILL! means the cells the result needs are not empty; clear the obstruction.