How do I split one cell into two cells in Excel?

Excel does not support splitting a single cell into two the way a Word table can. To divide a cell's contents, select it and use Data > Text to Columns to spread the pieces across neighboring columns, or use a formula such as TEXTSPLIT in Excel for Microsoft 365. To divide a cell visually, Microsoft's documented route runs the other way: merge the cells above it so the remaining cell looks like a split pair.

Can you split a cell in half in Excel?

Not literally. A worksheet cell sits at a fixed row and column intersection, and Microsoft's guidance is that an unmerged cell cannot be split. You can make a cell appear split by merging the cells above it, insert a new row or column to hold the extra data, or distribute the cell's contents into adjacent cells with Text to Columns.

How do I separate first and last names into two columns in Excel?

Three documented routes. Text to Columns: select the names, go to Data > Text to Columns, choose Delimited, tick Space, and finish. Flash Fill: type the first person's first name in the next column, press Enter, then press Ctrl+E or go to Data > Flash Fill. Formulas: =LEFT(A2,SEARCH(" ",A2,1)-1) returns the first name (Microsoft's pattern, trimmed of its trailing space) and =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)) the last name for two-part names; names with middle parts need the longer patterns Microsoft documents.

What formula splits text in Excel?

In every desktop version Microsoft lists, LEFT, MID, RIGHT, SEARCH, and LEN split text by character position: SEARCH finds the delimiter, LEFT takes what sits before it, RIGHT what follows. Excel for Microsoft 365 and Excel 2024 add TEXTSPLIT, which Microsoft describes as the Text to Columns wizard in formula form, plus TEXTBEFORE and TEXTAFTER for the piece either side of a delimiter.

Why did Text to Columns overwrite the next column?

Because the cells to the right are where the split pieces land: Microsoft's page warns that split contents overwrite the neighboring cells, so it tells you to make sure there is empty space there. Insert empty columns before running the wizard, or point its Destination box at an empty area so the originals stay put.

How to split cells in Excel: Text to Columns, names, and merged cells

Q: How do I split a merged cell back into separate cells?

Select the merged cell, then open the Merge & Center menu and choose Unmerge Cells. Do not expect the contents to redistribute: when cells are merged, only one cell's contents survive (the upper-left cell in left-to-right languages) and the rest are deleted, so unmerging restores the layout, not the lost data.

To split one cell’s contents across several columns, select the cell, go to Data > Text to Columns, choose Delimited, tick the character that separates the pieces, and select Finish. For the common case, that is all it takes: a column of full names, addresses, or codes that belongs in two or more columns.

If you arrived here wanting to cut a single cell into two, Excel cannot do it. Microsoft’s page on the subject states: “Unfortunately, Excel does not support splitting a single cell like Word tables do.” A worksheet cell sits at a fixed intersection of one row and one column, the smallest unit the grid has, so what looks like a cell split in half is usually one of two other things: contents distributed across neighboring cells, or a merged cell sitting beside unmerged ones. This page covers both jobs, and the name-splitting formulas in between.

Why can’t you split a cell in half in Excel?

A Word table is a layout tool, so its cells can be split or merged freely. An Excel worksheet is structured data, and Microsoft’s explanation is grid geometry: each cell’s position is fixed in advance, so the sheet grows by adding rows and columns, never by cutting a cell in half. Its page lists the two things Excel offers instead:

Split the contents of a cell into adjacent cells, across columns or down rows.
Insert new rows or columns to hold the additional data.

There is a third, purely visual answer on Microsoft’s merge page: merge the two cells above a cell, and the lone cell underneath looks split in two. So the practical question is which job you actually have. Delimited content that belongs in separate columns is a job for Text to Columns, in the next section. A cell that displays as one where its neighbors show two is a merge; unmerging is the last section.

How do I split a cell with Text to Columns?

Select the cell or column that contains the text. You can take as many rows as you want, but only one column at a time.
Check that the columns to the right are empty. Microsoft’s page warns that the split contents overwrite whatever sits in the next cells, so make sure there is empty space there, or plan to use the wizard’s Destination box instead.
Select Data > Text to Columns. The Convert Text to Columns Wizard opens.
Select Delimited, then Next.
Tick the delimiter that separates your data, Comma and Space for example, and check the result in the Data preview window. Then select Next.
Select the Destination where the split data should appear, and select Finish.

That six-step pass is enough for most delimited data. The wizard has more in it, including a final step that sets each new column’s format; Text to Columns in Excel walks the whole thing.

One platform note: Excel for the web does not have the Text to Columns Wizard. In the browser, Microsoft points instead at TEXTSPLIT, covered below.

How do I separate first and last names in Excel?

The single most common split job has three documented routes; pick by how messy the names are.

Text to Columns with a space delimiter. Run the wizard above, tick Space, and clear the other delimiter boxes. Microsoft’s own worked example is a column of full names split into first-name and last-name columns. For a list entered as last name first, Microsoft’s steps say to tick both Comma and Space.

Flash Fill. With full names in column A, type the first person’s first name into B2 and press Enter, then select Data > Flash Fill or press Ctrl+E. Flash Fill fills the rest of the column once it senses the pattern, and Microsoft names separating first and last names from a single column as one of its example uses. Microsoft recommends it for splits that are not consistent, because it works from the pattern you provide rather than from a fixed delimiter.

Formulas. The only route that stays live when the source names change; they get the next section.

What is the formula to split text in Excel?

Microsoft documents LEFT, MID, RIGHT, SEARCH, and LEN as the function toolkit for pulling a text string apart, covered function by function in extracting text in Excel, and one principle drives all of them: everything is a character position. A space marks a boundary, so in a cell holding only a first and last name, the last name starts after the first space, and SEARCH can find where that space is.

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

	A	B	C
1	Full name	First name	Last name
2	Jeff Smith	Jeff	Smith

SEARCH finds the space in A2 at position 5, so LEFT returns the four characters before it: B2 shows Jeff. C2 holds the last-name formula and shows Smith.

The last name is the mirror image, counted from the right:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

LEN counts the 10 characters of Jeff Smith, SEARCH returns 5 for the space, and RIGHT takes the last 5 characters: Smith. Both formulas come from Microsoft’s worked example for two-part names, with one adjustment:

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

Microsoft's published first-name formula stops at SEARCH itself, and by its own description returns five characters, ending at the space. Subtracting 1 trims that trailing space off the result.

The catch with the two-part pattern is that it is built for exactly two parts. A middle name or initial moves the last name to after the second space, and the formulas above will file “Eric S. Kurjan” as first name Eric, last name “S. Kurjan”. Microsoft’s functions page works through eleven name shapes, including middle initials, suffixes, and comma-first forms, if your list needs them.

TEXTSPLIT, TEXTBEFORE, and TEXTAFTER in Microsoft 365

Excel for Microsoft 365 and Excel 2024, on Windows and Mac, add three functions that make most position arithmetic unnecessary. The main addition is TEXTSPLIT: “The TEXTSPLIT function works the same as the Text-to-Columns wizard, but in formula form.” Pointed at the name above, =TEXTSPLIT(A2," ") returns Jeff and Smith side by side, spilling across two cells, and Microsoft’s first example for the function is exactly this, a name split at its spaces. TEXTSPLIT is also the split route Microsoft gives for Excel on the web, where the wizard does not exist.

The companion pair does the two halves separately: TEXTBEFORE returns the text before a given character or string, so =TEXTBEFORE(A2," ") is the first name, and TEXTAFTER returns the text after it, so =TEXTAFTER(A2," ") is the last name. In versions that lack these functions, the formula fails with the #NAME? error, Excel’s sign that it does not recognize a name in the formula, and the LEFT and RIGHT patterns above are the fallback.

How do I split a merged cell back into separate cells?

The other thing “split a cell” means: the rows around a cell show two cells where it shows one, and you want the dividing line back. That cell is not missing a split, it is merged. The fix is to select the merged cell, click the arrow next to Merge & Center, and choose Unmerge Cells. Excel for Mac keeps the same Unmerge Cells item in the Home > Merge & Center menu; Excel for the web has no Unmerge item, so there you select the merged cell and toggle the merge off with Home > Merge & Center, then Merge Cells. In Excel for Windows, the Find command will hunt down merged cells you have lost track of.

Before relying on a merge, know two things:

Merging deletes data. Merge several filled cells and Excel keeps what the upper-left cell held (in left-to-right languages); everything the other cells contained is removed. Unmerging restores the separate cells, but the removed contents are gone.
Merging is also the visual workaround. Microsoft’s merge page opens with it: merge the cells above an ordinary cell, and that cell now reads as if it had been split. If a layout genuinely needs one wide cell over two narrow ones, build it by merging the wide one.

Joining cells back together

Splitting has an exact opposite, and Microsoft’s merge page names it: distributing text into multiple cells is the reverse of concatenation, combining text from two or more cells into one. When the first-name and last-name columns need to become full names again, that is a formula job, covered in concatenate in Excel. It is not a job for Merge & Center: merging keeps only one cell’s contents, so joining text that way deletes everything else.

The short version

Excel cannot split a single unmerged cell into two; a cell is the grid’s smallest unit. Split its contents into neighboring cells, insert rows or columns, or merge neighbors to fake the look.
The main tool is Data > Text to Columns: Delimited, tick the separator, set a Destination, Finish. The split overwrites cells to the right, so clear space first. The full wizard, formats step included, is in Text to Columns in Excel.
Names: Text to Columns with Space, Flash Fill (Ctrl+E) for irregular lists, or =LEFT(A2,SEARCH(" ",A2,1)-1) and =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)) for a living formula split.
In Excel for Microsoft 365 and Excel 2024, =TEXTSPLIT(A2," ") is the wizard in formula form, and it is the split route Microsoft gives for Excel on the web; TEXTBEFORE and TEXTAFTER, in those same desktop versions, take either side of a delimiter.
A cell that shows one value where its neighbors show two is a merge: Merge & Center > Unmerge Cells. And after any bulk split, compare the before and after files to see every cell the operation touched.