How you split a cell in Excel depends on what you mean by “cell”. To split its contents into several cells — a full name into three columns, or “city, street, number” into parts — select the column, choose Data → Text to Columns, pick the delimiter and click Finish. To split a merged cell back, use Home → Merge & Center → Unmerge Cells.
A regular grid cell can’t be cut in two: it’s the smallest unit of a sheet. You can only split it visually with a diagonal line, or lay out the table so that the cell looks split. Below are all the options, with formulas that do the same automatically.
Text to Columns: split data by a delimiter
Text to Columns is a built-in Excel wizard that splits one column into several. It works in every version and suits one-off splits of large tables: a thousand rows split as fast as one.
- Select the cells of one column you want to split. Make sure there are enough empty columns to the right: the wizard writes the result over the neighboring cells.
- Go to Data → Text to Columns.
- Step 1: choose “Delimited” if the parts are separated by a character (space, comma, semicolon), or “Fixed width” if each part has a constant length.
- Step 2: tick the delimiter. Space, comma, semicolon and tab are in the list; type any other character into “Other”. “Treat consecutive delimiters as one” removes empty columns caused by double spaces.
- Step 3: choose a format for each resulting column. Use “Text” for SKUs and ZIP codes with leading zeros, otherwise 00123 becomes 123. In “Destination” you can pick another place so the source data isn’t overwritten.
- Click Finish.
The wizard splits only one column at a time and only to the right — it can’t split into rows. The result is plain values: when the source changes, you have to split again. For a live result use the TEXTSPLIT formula.
How to split a full name into separate cells
With the wizard: select the name column, Data → Text to Columns → Delimited → Space → Finish. First, middle and last names land in three neighboring columns.
In Excel 365 and 2024, a formula does it with one function:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Full name | First | Middle | Last |
| 2 | John Michael Smith | John=TEXTSPLIT(A2," ") | Michael | Smith |
| 3 | Sarah Jane Parker | Sarah=TEXTSPLIT(A3," ") | Jane | Parker |
| 4 | Lee Ann Wong | Lee=TEXTSPLIT(A4," ") | Ann | Wong |
The formula in B2 spills right into three cells; fill it down for the other rows.
In older versions, the parts are cut out by the positions of the spaces: FIND finds a space, LEFT and MID cut the piece.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Full name | First | Middle | Last |
| 2 | John Michael Smith | John=LEFT(A2,FIND(" ",A2)-1) | Michael=MID(A2,FIND(" ",A2)+1,FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A2)-1) | Smith=MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,100) |
| 3 | Sarah Jane Parker | Sarah=LEFT(A3,FIND(" ",A3)-1) | Jane=MID(A3,FIND(" ",A3)+1,FIND(" ",A3,FIND(" ",A3)+1)-FIND(" ",A3)-1) | Parker=MID(A3,FIND(" ",A3,FIND(" ",A3)+1)+1,100) |
| 4 | Lee Ann Wong | Lee=LEFT(A4,FIND(" ",A4)-1) | Ann=MID(A4,FIND(" ",A4)+1,FIND(" ",A4,FIND(" ",A4)+1)-FIND(" ",A4)-1) | Wong=MID(A4,FIND(" ",A4,FIND(" ",A4)+1)+1,100) |
First name — everything before the first space. Middle name — between the first and the second space: the second FIND starts searching after the first one. Last name — everything after the second space; 100 is just “enough characters”. With only two words, the middle and last name formulas return #VALUE!.
Another way is Flash Fill. Next to the first name, type the first name manually; in the next row press Ctrl+E, and Excel guesses the pattern and fills the column. Repeat for the other parts. It’s fast, but check the result: on unusual rows Excel can guess wrong.
Split a cell by number of characters
If the parts aren’t separated by a character but each has a constant length — codes, document numbers, dates without separators — choose “Fixed width” in step 1 of the wizard and click on the ruler to set the break lines. With formulas, LEFT, MID and RIGHT do the same.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Region | Number | Suffix |
| 2 | US-123456-01 | US=LEFT(A2,2) | 123456=MID(A2,4,6) | 01=RIGHT(A2,2) |
| 3 | DE-987654-12 | DE=LEFT(A3,2) | 987654=MID(A3,4,6) | 12=RIGHT(A3,2) |
| 4 | FR-555000-03 | FR=LEFT(A4,2) | 555000=MID(A4,4,6) | 03=RIGHT(A4,2) |
Region — the first 2 characters, number — 6 characters starting from the 4th, suffix — the last 2. The result is text, so “01” keeps its leading zero.
Split date and time in a cell
A date with time in Excel is one number: the integer part is the date, the fraction is the time. So you don’t need the wizard: the date is =INT(A2), the time is =A2-INT(A2). Format the result cells as Date and Time.
| A | B | C | |
|---|---|---|---|
| 1 | Date and time | Date | Time |
| 2 | 10/22/2026 14:30 | 10/22/2026=INT(A2) | 14:30=A2-INT(A2) |
| 3 | 11/01/2026 09:05 | 11/01/2026=INT(A3) | 09:05=A3-INT(A3) |
INT drops the fraction — what’s left is the date. Subtracting it from the original value leaves only the time. If the value is text, convert it first: --A2.
Split merged cells
To split a merged cell back, select it and on the Home tab click the arrow next to “Merge & Center” → Unmerge Cells. Another way: Ctrl+1 → Alignment tab → clear “Merge cells”.
After unmerging, the value stays only in the top-left cell; the others are empty. To fill them with the same value, select the range, press F5 → Special → Blanks, type = and the Up arrow, then press Ctrl+Enter. More in the article on how to fill blank cells with the value above.
How to split a cell diagonally
A diagonal is a border, not a real split: the cell still holds one text value. To draw it:
- Select the cell, press Ctrl+1 and open the Border tab.
- Click the diagonal line button at the bottom of the dialog — top-left to bottom-right or the other way — and click OK.
- To label both halves, type the first word, press Alt+Enter and type the second. Push the first line to the right with spaces and keep the second at the left edge. Adjust the row height and column width so the labels don’t cross the line.
For a line at another angle or in a merged cell, use Insert → Shapes → Line and drag it with Alt held down — the ends snap to the cell corners.
How to split a cell in half horizontally or vertically
You can’t cut one cell into two, but you can get the same look from the other side: merge all the other cells of that row (or column) in pairs and leave the one you need unmerged. For example, to make B2 look split in half horizontally, use rows 2 and 3: keep them separate in column B and merge A2:A3, C2:C3 and so on. Avoid this in tables you’ll sort and filter — merged cells get in the way.
Split a cell into rows
Text to Columns spills parts only to the right. If a cell holds a comma-separated list and each value should be on its own row, use =TEXTSPLIT(A2,,", ") — the delimiter is the third argument, so the parts go down. For a whole table where each value must keep its category, there’s Power Query and an add-in procedure — see how to split a cell into rows.
Other splitting tasks
- Separate text and numbers in a cell — when there’s no delimiter but the parts differ by character type.
- Get the first words and other words by position — when you need just one part of the text.
- Split a sheet into several sheets by column value — when you need to split the whole table, not a cell.
- The TEXTSPLIT function — all arguments in detail.
Why a cell won’t split: common mistakes
- Text to Columns is greyed out. Several columns are selected or the sheet is protected: select cells of one column.
- The wizard overwrote neighboring data. The result is written to the right of the source column — insert empty columns first or set the Destination.
- Leading zeros are gone, codes turned into dates. In step 3 of the wizard, set those columns to Text.
- Empty columns appeared. The text has double spaces — tick “Treat consecutive delimiters as one” or remove extra spaces first.
- It split in the wrong place. A value contains the delimiter itself, e.g. a comma in a company name. Set a text qualifier (quotes) or split by position with a formula.
- A merged cell won’t unmerge. The sheet is protected or the cell is in an Excel table — unprotect the sheet or convert the table to a range.
How to split a cell in Excel: FAQ
Can you split a cell in Excel into two?
Not a regular grid cell. You can unmerge a merged cell, split the contents of a cell into several cells (Data → Text to Columns) or split a cell visually with a diagonal border.
How do I split text in one cell into multiple cells?
Select the column → Data → Text to Columns → Delimited → choose the delimiter → Finish. With a formula: =TEXTSPLIT(A2," ") in Excel 365 and 2024.
How do I split first and last names into separate columns?
Data → Text to Columns with Space as the delimiter, or the formula =TEXTSPLIT(A2," ").
How do I split a cell diagonally?
Ctrl+1 → Border → the diagonal line button. Type the labels on two lines with Alt+Enter.
How do I unmerge cells?
Home → arrow next to Merge & Center → Unmerge Cells.
How do I split a cell into rows?
With =TEXTSPLIT(A2,,", "), with Power Query (Split Column → By Delimiter → Split into Rows) or with the “Unpivot and split 2nd column” procedure in !SEMTools.
Unpivot a table with categories into rows, split a sheet into sheets by column value, separate text and numbers in thousands of cells — all in a couple of clicks with !SEMTools.
!SEMTools solves this and hundreds of other tasks in a couple of clicks!
Summary: how to split a cell in Excel
| Task | Method |
|---|---|
| Contents by a delimiter | Data → Text to Columns |
| Same with a formula | =TEXTSPLIT(A2," ") |
| Names in any version | LEFT, MID and FIND |
| By number of characters | “Fixed width” or LEFT/MID/RIGHT |
| Date and time | =INT(A2) and =A2-INT(A2) |
| Merged cell | Home → Unmerge Cells |
| Diagonally | Ctrl+1 → Border → diagonal |
| Into rows | =TEXTSPLIT(A2,,", ") or “Unpivot and split 2nd column” |
I hope you found what you were looking for on this page and managed to split your cells. For a live result use TEXTSPLIT, for one-off processing — Text to Columns, and if you need to split the whole table rather than cells, see how to split a sheet by column value.