Split Text Into Separate Cells
Excel cannot really split a cell — a cell holds exactly one value — so splitting means writing its parts into the cells beside it. Three tools do that, and they behave very differently. Data > Text to Columns runs once and writes the pieces across the columns to the right, overwriting whatever sits there and never updating afterwards. =TEXTSPLIT(A2," ") is a live formula that re-splits itself whenever the source changes, but it needs Excel 365. Flash Fill (Ctrl+E) copies a pattern you demonstrate by typing one example, needs no delimiter at all, and is both the quickest option and the easiest one to get quietly wrong on irregular data.
The formula
=TEXTSPLIT(A2, " ") split across columns at every space (Excel 365)
=TEXTSPLIT(A2, , ",") split DOWN into rows instead — note the empty second argument
=SPLIT(A2, " ") the Google Sheets equivalent
=LEFT(A2, FIND(" ",A2)-1) the first word only, in any version of Excel
=MID(A2, FIND(" ",A2)+1, 999) everything after the first spaceA worked example
A2:A200 holds full names such as "Ada Lovelace" and "Grace Brewster Hopper". Columns B and C are empty.
Select A2:A200, then Data > Text to Columns > Delimited > Space > Finish
First names stay in column A and the remaining parts spill into B and C — Text to Columns overwrites the source column as well, and "Grace Brewster Hopper" becomes three cells while "Ada Lovelace" becomes two. That uneven width is the usual reason to reach for =TEXTSPLIT(A2," ") instead, or for Flash Fill, where you fix an odd row by typing the correct answer over it and pressing Ctrl+E again.
Which one do I need?
| If you want to… | Use |
|---|---|
| A one-off split, on any version of Excel | Data > Text to Columns — but insert enough blank columns to its right first, because it overwrites without asking twice |
| The split must update when the source cell changes | =TEXTSPLIT(text, delimiter) — a live spilled formula, Excel 365 only |
| Separating full names, including the ones with middle names | A name-specific approach — a plain space split misidentifies every three-part name |
| There is no consistent delimiter, but the pattern is obvious to a human | Flash Fill: type the result for the first row, press Ctrl+E, then check the rows that do not fit the pattern |
| The pieces should go down into rows rather than across into columns | =TEXTSPLIT(A2, , ",") — leave the column delimiter empty and pass the row delimiter as the third argument |
| You're in Google Sheets | =SPLIT(A2, " ") — same idea, different name; Sheets has no TEXTSPLIT |
| You actually want a cell divided diagonally on screen | Format Cells > Border > diagonal line. That is formatting only — the cell still holds one value, and the two labels are faked with Alt+Enter and spaces |
| You want to put split values back together | The reverse operation, with its own set of traps |
Frequently asked questions
Can you actually split a cell in Excel?
Not in the literal sense — one cell always holds one value. What every method really does is distribute the parts into neighbouring cells. The one exception is a cell that was previously merged: Home > Merge & Center toggled off will split it back into the cells it was made from.
Why did Text to Columns overwrite my other data?
It writes its output into the source column and as many columns to the right as it needs, replacing anything already there. Excel prompts once, but the prompt is easy to click through. Insert blank columns to the right of the source before running it.
How do I split one cell into two rows instead of two columns?
Use =TEXTSPLIT(A2, , ",") with the second argument left empty and the row delimiter in the third position, which spills the pieces downwards. Text to Columns cannot do this — it only ever writes across.
How do I split a cell diagonally in Excel?
That is a border, not a split: Format Cells > Border and pick one of the two diagonal buttons. The cell still contains a single value, so the two labels are usually typed as one entry with a line break (Alt+Enter) and padded with spaces to sit either side of the line.
New guides and tools, once a month
DE + EN · double opt-in · no spam