To split a cell in Excel, you generally split the content of one cell into multiple cells rather than physically dividing the cell itself. Here are the main methods to do this:
1. Using Text to Columns (Split Cell Content into Multiple Cells)
This is the most common way to split a cell’s content based on delimiters like spaces, commas, or fixed widths. Steps:
- Select the cell(s) you want to split.
- Go to the Data tab on the ribbon.
- Click Text to Columns.
- Choose Delimited if your data is separated by characters like spaces or commas, then click Next.
- Select the delimiter(s) (e.g., space, comma) that separates your data, then click Next.
- Choose the destination cell for the split data (to avoid overwriting original data) and click Finish.
If your data isn’t separated by delimiters but you want to split by character position, choose Fixed Width instead and set the break lines manually
2. Using Formulas to Split Cell Content
You can use text functions like LEFT()
, RIGHT()
, MID()
, combined with
SEARCH()
or FIND()
to extract parts of the text into separate cells. For
example, to split a full name into first and last names:
- First name:
=LEFT(A1, SEARCH(" ", A1)-1)
- Last name:
=RIGHT(A1, LEN(A1) - SEARCH(" ", A1))
This method is useful for dynamic splitting when data changes frequently
3. Using Flash Fill
Type the desired split data manually in adjacent cells for a couple of rows, then use Flash Fill :
- Select the next empty cell in the column.
- Go to Data > Flash Fill (or press
Ctrl + E
).
Excel will automatically fill the column based on the pattern you provided
4. Visually Splitting a Cell (Diagonal Split)
Excel does not allow physically splitting a single cell into two parts, but you can simulate it by:
- Adding diagonal borders to a cell.
- Formatting text with superscript and subscript to place two pieces of text diagonally in one cell.
This is mainly for visual effect, not actual data splitting
5. Splitting Cells in a Table (Rows or Columns)
If you are working with a table and want to split merged cells:
- Select the merged cell.
- Go to the Table Layout tab.
- Click Split Cells and specify how many rows or columns to split into
Summary
Method| Use Case| How to Access
---|---|---
Text to Columns| Split by delimiter or fixed width| Data tab > Text to Columns
Formulas| Dynamic splitting with text functions| Enter formulas in adjacent
cells
Flash Fill| Pattern-based quick splitting| Data tab > Flash Fill or Ctrl+E
Visual Split (Diagonal)| Simulate split for display only| Cell formatting >
Borders / Font options
Split Cells in Table| Split merged cells in tables| Table Layout tab > Split
Cells
These methods cover most scenarios for splitting cells or their content in Excel