You might want to split a cell into two smaller cells within a single column. Unfortunately, Excel does not support splitting a single cell like Word tables do.Â
In Word, a table is a layout tool for arranging text on a page. Cells can be split or merged freely because they don’t represent structured data.
In Excel, a cell is the smallest unit of data. Every cell must live at a specific row and column intersection. Excel worksheets are like graph paper — each square already has a fixed position, so you add more squares instead of cutting one in half. Because of that, Excel cannot split a single cell into multiple cells the way Word can.
Instead, Excel lets you:
-
Split the contents of a cell into adjacent cells (columns or rows), or
-
Insert new rows or columns to hold additional data.
See the following screenshots for an example:
Split the content from one cell into two or more cells
Note:Â Excel for the web doesn't have the Text to Columns Wizard. Instead, you can Split text into different columns with functions.
-
Select the cell or cells whose contents you want to split. For example,
Important:Â When you split the contents, they will overwrite the contents in the next cell to the right, so make sure to have empty space there.
-
Select Data > Data Tools > Text to Columns.
-
Choose the delimiter or delimiters to define the places where you want to split the cell content, and then select Apply. Use Custom to split at comma + space.
-
The cell is spit across three columns.
Note:Â You can transpose columns to rows. After you finish splitting the cell, copy the results then choose Paste Special > Transpose Rows and Columns.
Use Flash Fill to split cells
When your text split isn't consistent or you just want Excel to "figure it out", you can use Flash Fill. This is good when you want to separate names from a single column or combine first and last names from two different columns.Â
For an example, see Using Flash Fill in Excel.
Split cell into two or more columns or rows using TEXTSPLIT (Excel on the web)
In Excel on the web, if your data is in one cell and separated by a delimiter (comma, semicolon, line break, etc.) you can use the TEXTSPLITÂ function.
-
Enter "Apple, Banana, Cherry" into a cell A1.
-
In the cell immediately to the right, enter the formula =TEXTSPLIT(A1,,", ") and press Enter.
The result is the column is split into three rows at the delimiter.
See Also
