How to Split Cells in Excel
Excel offers three ways to split cells: Text to Columns (the classic), Flash Fill (Excel 2013+), and TEXTSPLIT (Excel 365). Each fits a different situation. Flash Fill is often the fastest.
⚡ Quick Answer
For simple splits, use Flash Fill: type the desired result in the adjacent column, press Ctrl + E. For classic splits, use Data → Text to Columns → Delimited. For live formulas that update, use =TEXTSPLIT(A1," ").
Method 1: Text to Columns (classic)
Best when you're happy to replace the original cell with the split result.
Select the cells to split
Highlight the column of cells you want to split.
Open Data → Text to Columns
The Convert Text to Columns Wizard opens.
Choose Delimited or Fixed Width
Delimited: values are separated by a character (comma, tab, space, custom). Fixed Width: values are separated by column position.
Pick your delimiter
Tick the delimiter — Space, Comma, Semicolon, Tab, or a custom character. Preview shows how the split will look.
Set the destination
Optional: change where the split values go (default overwrites source). Click Finish.
Method 2: Flash Fill (fastest for patterns)
Flash Fill watches your typing, detects the pattern, and fills the rest. Works for splitting, formatting, extracting, and more.
Type the first result manually
In the column next to your data, type what you want the result to look like for the first row. Example: if A1 = "John Smith", type "John" in B1.
Press Ctrl + E
Excel auto-detects the pattern and fills B2:B(n) with the corresponding first names.
Confirm or adjust
If Flash Fill picks the wrong pattern, undo (Ctrl + Z) and give it more examples. Two or three typed examples usually settle it.
It handles more than simple splits: reformatting dates (2024-01-05 → Jan 5, 2024), extracting middle names, capitalizing text, joining first + initial + last. Whenever you're about to write a formula, try Flash Fill first — often it just works.
Method 3: TEXTSPLIT function (Excel 365)
Creates a live formula that updates when source data changes.
Click an empty cell
Location where you want the split result to appear.
Enter TEXTSPLIT formula
Syntax:
=TEXTSPLIT(text, col_delimiter, [row_delimiter]). Example:=TEXTSPLIT(A1," ")splits A1 on spaces into multiple columns.Press Enter
Results spill across cells. Editing A1 automatically updates the spill.
TEXTSPLIT examples
=TEXTSPLIT("John Smith", " ") → ["John", "Smith"]
=TEXTSPLIT("a,b,c", ",") → ["a", "b", "c"]
=TEXTSPLIT("row1|row2", , "|") → ["row1"; "row2"] (vertical split)Method 4: Formulas (LEFT, RIGHT, MID, FIND)
Fallback for older Excel versions without TEXTSPLIT or Flash Fill.
Split a full name into first and last
First name: =LEFT(A1, FIND(" ", A1) - 1)
Last name: =MID(A1, FIND(" ", A1) + 1, LEN(A1))Split by first delimiter
Before delimiter: =LEFT(A1, FIND("@", A1) - 1)
After delimiter: =MID(A1, FIND("@", A1) + 1, LEN(A1))Comparison of methods
| Method | Speed | Live updating? | Requires |
|---|---|---|---|
| Text to Columns | Fast | No (one-time split) | Any Excel version |
| Flash Fill | Very fast | No | Excel 2013+ |
| TEXTSPLIT | Fast | Yes | Excel 365 |
| LEFT/RIGHT/MID | Slow to write | Yes | Any Excel version |
Keyboard shortcut
Split Cell Shortcuts
Excel Wizard — split messy data no delimiter can handle
Text to Columns and TEXTSPLIT work when data is clean. Real data is messy: "John Q. Smith Jr., PhD" or "555 Main St #4B, Apt Building." Excel Wizard's AI understands what you want ("split into first name, last name, suffix") and does it, no formulas required. Handles inconsistent formats across rows.
Install Excel Wizard →Frequently asked questions
How do I split a cell in Excel by a delimiter?
Use Text to Columns. Select the cells, go to Data → Text to Columns, choose Delimited, select the delimiter (space, comma, semicolon, tab, or custom character), preview the split, then click Finish. Excel splits the values across adjacent cells.
Can I split cells without deleting the original?
Yes. Use TEXTSPLIT (Excel 365) or LEFT/RIGHT/MID functions in adjacent cells. TEXTSPLIT syntax: =TEXTSPLIT(A1," ") splits A1 on spaces into multiple cells. This leaves A1 intact and creates a spilled array of results in adjacent cells.
How do I split a full name into first and last name?
Three methods: (1) Text to Columns with space as delimiter — fast but destructive. (2) Flash Fill — type the first name pattern in the adjacent column, press Ctrl+E, Excel fills the rest. (3) Formulas: =LEFT(A1,FIND(" ",A1)-1) for first name, =MID(A1,FIND(" ",A1)+1,LEN(A1)) for last name. Flash Fill is usually the easiest.
What is Flash Fill in Excel?
Flash Fill detects patterns in your data entry and fills the rest automatically. Type an example of what you want in the first row (like the first name from a full name column), and press Ctrl+E — Excel fills the pattern down. Works for splitting, combining, formatting, extracting substrings, and more. Requires Excel 2013 or later.