Excel guide · 5 min read
How to split a cell in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Full name | Location | First | Last |
| 2 | Maria Lopez | Austin, TX | Maria | Lopez |
| 3 | James Carter | Denver, CO | James | Carter |
| 4 | Chen Wei Zhang | Seattle, WA | Chen Wei | Zhang |
| 5 | Aisha Bello | Atlanta, GA | Aisha | Bello |
| 6 | Tom Novak | Cleveland, OH | Tom | Novak |
| 7 | Priya Nair | San Jose, CA | Priya | Nair |
Download this sheet as an Excel practice file (.xlsx)
What do you mean by "split"?
Pick the method by what you're trying to do:
- The cell spans several columns because it was merged: unmerge it (Method 1).
- One cell holds two pieces of data, like Maria Lopez or Austin, TX: split the text into separate columns (Methods 2 to 4).
- You want a corner header with two labels, like a timetable: draw a diagonal border (Method 5).
- You want two smaller cells inside one row: insert a column, then merge the cells above it so the heading spans both.
Method 1: unmerge a merged cell
If the "cell" you want to split covers several columns or rows, it's a merged cell, and unmerging gives you the original cells back. Microsoft notes you can only split a cell this way if it was merged.
- Select the merged cell.
- Go to Home and click the arrow next to Merge & Center.
- Click Unmerge Cells.
- The content stays in the top-left cell and the others are empty.
What if Merge & Center is greyed out?
You may still be editing a cell (press Enter or Esc first), the sheet may be protected, or the cells may be in an Excel table, which can't hold merged cells. See can't merge cells for the fixes.
Method 2: Text to Columns
This splits the text of each cell across the columns to its right. It's built in, works in every desktop version, and is best for a one-off clean-up. The results are plain values, so they won't update if the original changes.
- Make sure the columns to the right are empty. Text to Columns overwrites whatever is there without asking.
- Select the cells, for example B2:B7.
- Go to Data > Text to Columns (in the Data Tools group).
- Choose Delimited and click Next.
- Tick Comma, and Space if you want to drop the space after it, or tick Other and type the character. The preview shows the split.
- Click Next, choose the Destination cell if you want the results somewhere else, and click Finish.
Does Text to Columns work in Excel for the web?
No. Microsoft says Excel for the web doesn't have the Text to Columns wizard and points to formulas instead.
Method 3: split with a formula (Excel 365)
Formulas keep the original intact and update when it changes. In Excel 365, TEXTBEFORE and TEXTAFTER take the text before or after a delimiter. A -1 for the third argument means "the last one", which handles middle names. Column C of the sample uses it.
Result: Chen Wei
Chen Wei: everything before the last space. =TEXTAFTER(A4," ",-1) in column D returns Zhang. For Maria Lopez the same formulas give Maria and Lopez.
How do I split into several columns at once?
TEXTSPLIT spills one piece per column. Leave space to the right, or it returns #SPILL!.
Result: spills 1 row × 2 columns
| Austin | TX |
Austin and TX in two cells side by side. The delimiter is a comma followed by a space, so neither piece keeps a stray space.
Can I split a cell into rows instead of columns?
Yes. Leave the column delimiter empty and give TEXTSPLIT a row delimiter.
Result: spills 2 rows × 1 column
| Austin |
| TX |
Austin above TX, one piece per row.
Method 3b: formulas for older Excel
Without TEXTBEFORE, combine LEFT and FIND. FIND returns the position of the space, and LEFT keeps everything before it. For the last name, use MID from one character after the space. These work in every version but split at the first space, so a middle name ends up in the last-name column.
Result: Maria
Maria: the characters before the first space. =MID(A2,FIND(" ",A2)+1,100) returns Lopez.
Method 4: Flash Fill
Flash Fill copies a pattern from an example. It's handy when the text isn't split by one consistent character. Like Text to Columns, the results are static values.
- In C2, type the first part the way you want it, for example Maria.
- Select C3 and press Ctrl+E, or go to Data > Flash Fill.
- Check the results, especially rows with middle names or extra spaces, and correct one example if needed. Flash Fill updates the rest from the new pattern.
Method 5: make a corner cell look split (diagonal border)
Timetables and price grids often have a top-left cell split diagonally, with one label for the rows and one for the columns. It's one cell with two lines of text and a diagonal border.
- In the corner cell, type the column label (for example Day), press Alt+Enter for a new line, and type the row label (Time).
- Put a few spaces in front of the first label so it sits to the right.
- Press Ctrl+1, go to the Border tab and click the diagonal button that runs from top-left to bottom-right, then OK.
- Widen the column and raise the row height until the line runs between the two labels.
Common mistakes
Running Text to Columns next to data
It overwrites the columns to the right without warning. Insert empty columns first or set a Destination.
Splitting names at the first space
Middle names and two-word surnames end up in the wrong column. Use TEXTBEFORE and TEXTAFTER with -1, and check the odd rows.
Merging cells to fake a split
Merged cells break sorting, filtering and copying. Use Center Across Selection under Format Cells > Alignment for headings instead.
Expecting Flash Fill results to update
They're typed values. Use a formula if the source will change.
FAQ
Can you split a cell in half in Excel?
Not a single unmerged cell. Insert a column next to it and merge the cells above so the heading spans both, or draw a diagonal border for a header.
How do I split one cell into two rows in Excel?
Insert a row, then merge the cells next to it that should stay as one. To split the text itself into rows, use TEXTSPLIT with a row delimiter, such as =TEXTSPLIT(B2,,", ").
How do I split a cell diagonally in Excel?
Type two labels on two lines with Alt+Enter, then Ctrl+1 > Border and click a diagonal border button.
How do I split text in a cell by a comma?
Use Data > Text to Columns with Comma ticked, or =TEXTSPLIT(A2,",") in Excel 365. TRIM the results if a space follows each comma.
How do I split a cell in Excel for the web?
Excel for the web has no Text to Columns. Use TEXTSPLIT, TEXTBEFORE or TEXTAFTER formulas, or unmerge from the Merge & Center menu.
Functions used
TEXTBEFOREText before a delimiterTEXTAFTERText after a delimiterTEXTSPLITSplits text into cells, spillsLEFTCharacters from the startFINDPosition of text (case-sensitive)MIDCharacters from the middle
Related guides
- How to split text by a delimiter in Excel
- Why can't I merge cells in Excel? Causes and fixes
- How to separate first and last name in Excel
- How to split an address into columns in Excel
- How to add borders in Excel
- How to extract the first, last or nth word in Excel
Updated