How to Split a Cell in Excel

Excel doesn’t let you split one unmerged cell into two. What you can do is unmerge a merged cell with Home > Merge & Center > Unmerge Cells, or split a cell’s text into separate columns with Data > Text to Columns. Flash Fill is another quick way to separate text like first and last names, and the TEXTSPLIT formula does the same job in Excel for Microsoft 365 and Excel for the web.

This guide covers Excel for Microsoft 365, Excel 2024, 2021, 2019, and 2016 on Windows. It explains why a cell can’t be cut in half, then walks through every practical way to split cell contents, plus how to make one cell look split.

Why You Can’t Split a Single Cell

Microsoft explains that in Excel a cell is the smallest unit of data, fixed at one row and one column, so it can’t be split the way a Word table cell can. Instead, you move the contents into neighboring cells, or add rows and columns to hold the extra data.

Method 1: Unmerge a Merged Cell

If a cell spans several columns because it was merged, you can split it back into its original cells.

1. Select the merged cell.
2. On the Home tab, select the arrow next to Merge & Center.
3. Select Unmerge Cells. Result: the cell splits back into separate cells, and the contents stay in the top-left one.

Method 2: Split Text Into Columns With Text to Columns

Use this when every cell follows the same pattern, such as names separated by a space or values separated by commas. To do the reverse, see how to combine first and last names in Excel.

1. Insert empty columns to the right of the data, so nothing gets overwritten. Microsoft warns that split results overwrite whatever is in the next cells.
2. Select the cells to split. Text to Columns works on one column at a time.
3. Select Data > Text to Columns. Result: the Convert Text to Columns Wizard opens.
4. Choose Delimited to split at a comma, space, or other character, or Fixed width to split at set positions, and select Next.
5. Pick the delimiter (check Treat consecutive delimiters as one if there are double spaces) and look at the preview. Select Next.
6. Optionally set a column data format and a Destination cell, then select Finish. Result: the text is spread across the columns.

Microsoft notes that Excel for the web doesn’t have the Text to Columns Wizard. There, use the TEXTSPLIT formula below instead. To split into rows rather than columns, split into columns first, then copy the results and use Paste Special > Transpose.

Method 3: Use Flash Fill

Flash Fill works well when the pattern is easy to see but not perfectly consistent.

1. In the column next to your data, type how the first result should look, such as the first name only.
2. Press Enter, then press Ctrl + E (or select Data > Flash Fill). Result: Excel fills the rest of the column by following your example.
3. Repeat in the next column for the second part, such as the last name.

Check the results. Flash Fill guesses from your example, so unusual entries like middle names can come out wrong. Unlike a formula, Flash Fill results don’t update if the original data changes.

Method 4: Split Text With a Formula

In Excel for Microsoft 365 and Excel for the web, the TEXTSPLIT function works like Text to Columns in formula form, and it updates automatically.

• =TEXTSPLIT(A2," ") splits the text in A2 at each space into separate columns.
• =TEXTSPLIT(A2,",") splits at commas.
• =TEXTSPLIT(A2,,",") splits at commas down rows instead of across columns, using the row delimiter argument.
• Add TRUE as the fourth argument to ignore empty values from double delimiters, for example =TEXTSPLIT(A2," ",,TRUE).

Make sure the cells to the right (or below) are empty, or the formula shows a #SPILL! error. In older versions such as Excel 2019, combine LEFT, RIGHT, MID, and FIND instead. For example, =LEFT(A2,FIND(" ",A2)-1) returns everything before the first space.

Example: split “Jane Smith” in A2 into first and last names

• First name (Microsoft 365): =TEXTBEFORE(A2," ")
• Last name (Microsoft 365): =TEXTAFTER(A2," ")
• First name (any version): =LEFT(A2,FIND(" ",A2)-1)
• Last name (any version): =RIGHT(A2,LEN(A2)-FIND(" ",A2))

Enter the formulas in B2 and C2, then drag them down the column. These work for names with exactly one space; names with a middle name need a different split.

Method 5: Split a Column With Power Query

For large or repeating imports, Power Query can split a column and repeat the same steps every time you refresh.

1. Select a cell in the data and choose Data > From Table/Range. Result: the Power Query Editor opens.
2. Select the column, then choose Home > Split Column > By Delimiter.
3. Pick the delimiter and where to split, and select OK.
4. Select Close & Load to put the results in a new sheet.

See Microsoft’s Power Query split guide for splitting by number of characters and other options.

Make One Cell Look Split

• Diagonal line: right-click the cell, choose Format Cells > Border, and add a diagonal border. Type one label, press Alt + Enter for a new line, then type the second and adjust the alignment. It’s still one cell. The same Border tab is also where you can make a thicker bottom border in Excel.
• Split a header over two columns: One trick is to insert a column, narrow both columns, and merge the cells above and below, so one row looks divided while others don’t.

If you just need one cell to look bigger, see whether you can resize a single cell in Excel.

Troubleshooting

• Text to Columns is grayed out: it only works on one column at a time, and not while you’re editing a cell. Press Esc and select a single column of cells.
• Data was overwritten: press Ctrl + Z right away, insert empty columns, and run it again.
• #SPILL! error with TEXTSPLIT: clear the cells where the results need to go.
• Numbers lost leading zeros: in the last step of Text to Columns, set that column’s format to Text.

Frequently Asked Questions

Can I split a cell diagonally?

Not truly. You can add a diagonal border with Format Cells > Border and place text on each side, but it’s still one cell.

Can I split text with a formula?

Yes. In Microsoft 365, =TEXTSPLIT(A2," ") splits the text in A2 at each space.

How do I split first and last names?

Use Text to Columns with a space as the delimiter, or type the first name next to the first entry and press Ctrl + E for Flash Fill.