Cellwise Start free

Excel guide · 5 min read

How to split a cell in Excel

Quick answerExcel can't split a single cell in two the way Word splits a table cell, because every cell is fixed to one row and one column. What you can do: unmerge a merged cell with Home > Merge & Center > Unmerge Cells, split the text in a cell across columns with Data > Text to Columns or a formula such as TEXTSPLIT, or make a header cell look split with a diagonal border.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCD
1Full nameLocationFirstLast
2Maria LopezAustin, TXMariaLopez
3James CarterDenver, COJamesCarter
4Chen Wei ZhangSeattle, WAChen WeiZhang
5Aisha BelloAtlanta, GAAishaBello
6Tom NovakCleveland, OHTomNovak
7Priya NairSan Jose, CAPriyaNair

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.

  1. Select the merged cell.
  2. Go to Home and click the arrow next to Merge & Center.
  3. Click Unmerge Cells.
  4. 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.

  1. Make sure the columns to the right are empty. Text to Columns overwrites whatever is there without asking.
  2. Select the cells, for example B2:B7.
  3. Go to Data > Text to Columns (in the Data Tools group).
  4. Choose Delimited and click Next.
  5. 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.
  6. 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.

=TEXTBEFORE(A4," ",-1)

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!.

=TEXTSPLIT(B2,", ")

Result: spills 1 row × 2 columns

AustinTX

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.

=TEXTSPLIT(B2,,", ")

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.

=LEFT(A2,FIND(" ",A2)-1)

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.

  1. In C2, type the first part the way you want it, for example Maria.
  2. Select C3 and press Ctrl+E, or go to Data > Flash Fill.
  3. 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.

  1. In the corner cell, type the column label (for example Day), press Alt+Enter for a new line, and type the row label (Time).
  2. Put a few spaces in front of the first label so it sits to the right.
  3. Press Ctrl+1, go to the Border tab and click the diagonal button that runs from top-left to bottom-right, then OK.
  4. 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

Related guides

Updated