How to Split a Cell

The best method depends on your specific data and needs. Text to Columns is a quick built-in tool, Flash Fill is great for patterns, formulas provide flexibility, and Power Query is powerful for more complex splits. Try out the different approaches to see which works best for your situation.

Split Cells in Excel

There are several ways to split cells in Excel:

Text to Columns

Select the cell or range of cells you want to split.
Go to the Data tab and click Text to Columns in the Data Tools group.
In the Text to Columns Wizard, choose whether your data is Delimited or Fixed Width, then click Next.
Select the delimiter(s) that separate the data you want to split, then click Next.
Choose the destination for the split data and click Finish.

Flash Fill

Enter the first split value manually in an adjacent cell.
Select that cell and press Ctrl+E (or go to Data > Flash Fill) to automatically fill the remaining cells.

Formulas

Use a combination of functions like LEFT, RIGHT, FIND, and LEN to extract the parts of the cell you want to split.
For example, to split a full name into first and last name:

- First name: =LEFT(A1,FIND(” “,A1)-1)
- Last name: =RIGHT(A1,LEN(A1)-FIND(” “,A1))

Power Query

Select the cell or range you want to split.
Go to the Data tab and click Get & Transform Data > From Table/Range.
In the Power Query editor, select the column you want to split.
Go to Transform > Split Column > By Delimiter and choose the delimiter.
Click OK to split the column.

Shortcuts for Splitting Cells Quickly

The shortcut keys and Flash Fill are the quickest for simple splits, while Text to Columns, formulas, and Power Query offer more flexibility for complex scenarios.

Splitting Cells in Excel

Shortcut Key