Excel users often need to reorganize information that has been entered into a single cell, column, or row. A full name may need to become first and last names, an address may need to be separated into street, city, and postal code, or a combined row of data may need to be rearranged for reporting. Although Excel does not technically “split” a normal cell into smaller independent cells the way a table in a word processor might, it offers several practical tools that achieve the same result.
TLDR: To split cells in Excel, a user usually separates the contents of one cell into multiple columns or rows. The most common tools are Text to Columns, formulas such as TEXTSPLIT, LEFT, RIGHT, and MID, and options for unmerging cells. Columns are best for separating structured text, while rows are useful when one cell contains multiple items that should become separate records. The best method depends on whether the data is separated by commas, spaces, line breaks, or a fixed character pattern.
Table of Contents
What “Splitting Cells” Means in Excel
In Excel, the phrase split cells can refer to several different tasks. A user may want to split text inside one cell into multiple columns, divide one cell’s contents into multiple rows, separate merged cells, or visually divide information using formatting. Understanding the exact goal is important because Excel handles each situation differently.
For example, if a cell contains “Maria Lopez”, splitting it into two columns would place Maria in one cell and Lopez in another. If a cell contains a list such as “Apples, Oranges, Bananas”, splitting it into rows would place each fruit on its own row. If cells have been merged, splitting means using the Unmerge Cells command to return them to separate cells.
Splitting Cells into Columns with Text to Columns
The Text to Columns feature is one of the most widely used methods for splitting data in Excel. It is especially helpful when information follows a predictable separator, also known as a delimiter. Common delimiters include spaces, commas, semicolons, tabs, and line breaks.
To split a cell or column into multiple columns using Text to Columns, a user would generally follow these steps:
- Select the cell or column containing the combined data.
- Go to the Data tab on the Excel ribbon.
- Click Text to Columns.
- Choose Delimited if the data is separated by characters such as commas or spaces.
- Choose the delimiter that matches the data.
- Preview the result in the wizard.
- Select a destination cell if the original data should be preserved.
- Click Finish.
This method works well for names, addresses, product codes, imported CSV data, and lists separated by punctuation. However, it is important to select a blank destination area if the split data might overwrite existing information. Excel will place the separated values into neighboring columns, so any existing content in those cells may be replaced.
Delimited vs. Fixed Width Splitting
When using Text to Columns, Excel gives two main options: Delimited and Fixed Width. The correct choice depends on how the data is structured.
- Delimited: Best when data is separated by a specific character, such as a comma, space, tab, or semicolon.
- Fixed Width: Best when data has a consistent number of characters in each section.
For instance, a cell containing “North, Sales, 2025” should be split using a comma delimiter. A code like “ABC12345NY” may be better handled with fixed width if the first three characters represent a category, the next five represent an ID, and the last two represent a state.
Splitting Full Names into First and Last Names
One common Excel task is separating full names. If names are consistently formatted as first name followed by last name, the space delimiter in Text to Columns can split them quickly. For example, “Jonathan Reed” becomes Jonathan in one column and Reed in the next.
However, names can be more complicated. A name such as “Mary Ann Smith” may split into three columns if spaces are used as the delimiter. In that case, formulas or Power Query may provide better control. The user should review the data first and decide whether the structure is consistent enough for a simple split.
Using Formulas to Split Cells into Columns
Excel formulas can split cells dynamically, meaning the results update when the original data changes. This is useful when the source data may be edited or refreshed later.
In modern versions of Excel, the TEXTSPLIT function is often the easiest formula-based solution. For example:
=TEXTSPLIT(A2," ")
This formula splits the contents of cell A2 wherever a space appears. If A2 contains “Daniel Carter”, the formula places Daniel and Carter into separate columns.
Other useful formulas include:
LEFT: Extracts characters from the left side of a cell.RIGHT: Extracts characters from the right side of a cell.MID: Extracts characters from the middle of a cell.FIND: Locates the position of a specific character.LEN: Counts the number of characters in a cell.
For example, if a full name is in A2, the first name can be extracted with:
=LEFT(A2,FIND(" ",A2)-1)
The last name can be extracted with:
=RIGHT(A2,LEN(A2)-FIND(" ",A2))
These formulas work best when the data follows a predictable pattern. If names or entries contain extra spaces, inconsistent punctuation, or missing values, additional cleanup may be required.
Splitting Cells into Rows
Sometimes the goal is not to create new columns but to turn multiple values in one cell into separate rows. This is common when a cell contains several items, such as tags, product names, email addresses, or survey responses.
For modern Excel users, TEXTSPLIT can split data into rows by using the row delimiter argument. If cell A2 contains “Red, Blue, Green”, the formula below can place each color in its own row:
=TEXTSPLIT(A2,,", ")
The empty second argument tells Excel there is no column delimiter, while “, ” is used as the row delimiter. The result spills vertically into multiple rows.
Another strong option for splitting into rows is Power Query. Power Query is useful for larger datasets because it can transform data in a repeatable way. A user can load a table into Power Query, select the column to split, choose Split Column, and then select By Delimiter. In the advanced options, Excel allows splitting into Rows instead of columns.
Using Power Query to Split Data
Power Query is one of Excel’s most reliable tools for cleaning and reshaping data. It is especially helpful when the same splitting process must be repeated regularly, such as with monthly reports or exported system data.
To split a column using Power Query, the user can follow these general steps:
- Convert the data range into a table by selecting it and pressing Ctrl + T.
- Go to the Data tab.
- Select From Table/Range.
- In Power Query, select the column that should be split.
- Choose Split Column, then By Delimiter.
- Select the delimiter, such as comma, space, or custom character.
- Choose whether to split into columns or rows.
- Click Close & Load to return the transformed data to Excel.
Power Query does not simply change the original cells; it creates a transformation process. This means the user can refresh the query later when new data is added, making it a powerful option for recurring work.
Splitting Merged Cells
Another common request is splitting a merged cell. In Excel, merged cells combine two or more cells into one larger cell. To split them again, the user should unmerge the cells.
The process is simple:
- Select the merged cell.
- Go to the Home tab.
- Click the arrow next to Merge & Center.
- Select Unmerge Cells.
After unmerging, Excel restores the original cell grid. However, only the upper-left cell usually retains the original value. The other cells become blank. If the same value needs to appear in each unmerged cell, it must be filled manually, with formulas, or by using Go To Special and fill techniques.
Splitting Cells Visually with Formatting
Excel does not allow a standard cell to be divided into two independent editable sections. However, a user can create the appearance of a split cell with formatting. This may be useful for headings, labels, or printed forms.
One method is to use a diagonal border. The user can right-click a cell, choose Format Cells, open the Border tab, and apply a diagonal line. Text can then be arranged with spaces or line breaks to appear on different sides of the diagonal.
This approach is visual only. It does not create two separate cells, and Excel cannot calculate the two areas independently. For actual data analysis, the information should be placed in separate cells, columns, or rows.
Best Practices Before Splitting Data
Before splitting cells, users should prepare the worksheet carefully. Splitting data can overwrite nearby cells, create unexpected blanks, or produce inconsistent results if the source data is messy.
- Make a backup: A copy of the worksheet protects the original data.
- Check delimiters: The user should confirm whether the data is separated by commas, spaces, tabs, or line breaks.
- Remove extra spaces: The
TRIMfunction can clean unnecessary spaces. - Use a blank destination: Split results should not overwrite important data.
- Preview results: Text to Columns and Power Query both provide previews before final changes are applied.
Choosing the Right Method
The best method depends on the type of split required. For a quick one-time column split, Text to Columns is usually the easiest choice. For dynamic results that update automatically, formulas such as TEXTSPLIT are more flexible. For large datasets or repeated workflows, Power Query is often the most professional option.
If the goal is to undo merged cells, the user should use Unmerge Cells. If the goal is only visual design, borders and formatting may be enough. The key is to distinguish between splitting text, splitting rows, unmerging cells, and creating a visual layout.
Common Mistakes to Avoid
- Overwriting existing data: Split results may fill adjacent columns or rows, replacing content if space is not available.
- Using the wrong delimiter: Choosing spaces instead of commas, for example, can produce too many columns.
- Ignoring inconsistent data: Entries with missing separators may not split correctly.
- Confusing visual splits with data splits: A diagonal border does not create separate editable cells.
- Forgetting merged cell behavior: Unmerging keeps the value only in the upper-left cell.
FAQ
Can Excel split one cell into two smaller cells?
Excel cannot split a normal single cell into two independent cells. It can split the contents of a cell into other cells, or it can unmerge cells that were previously merged.
What is the easiest way to split text into columns?
The easiest method is usually Text to Columns. It works well when the data is separated by a clear delimiter, such as a comma, space, or tab.
How can Excel split cells into rows?
Modern Excel can use TEXTSPLIT to split values vertically. Power Query can also split a column by delimiter and place the results into separate rows.
Does splitting cells delete the original data?
It depends on the method. Text to Columns may replace the original column unless a different destination is selected. Formulas and Power Query can preserve the source data.
How does a user split merged cells?
The user should select the merged cell, go to Home, open Merge & Center, and choose Unmerge Cells.
Which formula is best for splitting text in Excel?
In newer Excel versions, TEXTSPLIT is the most convenient. In older versions, combinations of LEFT, RIGHT, MID, FIND, and LEN are commonly used.