Split cells in Excel
We can split a cell in Excel by adding a new column and then merging cells. We can also split the content of a cell into multiple cells by using Text to Column Wizard, Flash Fill, and formulas.
Splitting a Cell
1. Smith is scheduled to begin at 8:00 am and will work until 10:00 am. If we like Smith to start at 8:30, we are going to split cell C5 and color it accordingly.
2. Select column D. Right-click, and then click Insert.
Result: Inserted column will look like below.
3. Select columns C and D and resize them to 64 pixels (each one of them being 32 pixels) using the fill handle.
4. Select C3 and D3. Go to your Home tab and locate the Alignment group. Click the down arrow next to Merge & Center and click Merge Cells.
5. Now repeat step 4 for C4-D4 and C6-D6. Change the background color of C5 to no fill. The result is given below.
Using Wizard to Split Cells
1. We are going to split the content of a cell into multiple cells. Select the range containing a list of full names separated by a comma.
2. On the Data tab, click “Text to Columns”.
3. The following dialog box will appear. Select Delimited and click Next.
4. Clear all the checkmarks except “comma” as a delimiter. Click Next and then finish.
Result: Below is the result. Please note that a comma was used as a delimiter in this example. If you have another delimiter in your dataset, you can use the same approach to split cells.
Flash Fill to Split Cells
We can also use Flash Fill in Excel to split cells and automatically extract information to a new cell. Flash fill works perfectly when Excel can recognize a pattern.
1. Enter the value 200,000 in cell C3 to tell Excel what number to extract from cell B3.
2. On the Data tab, click “Flash Fill” (Keyboard shortcut: CTRL+e).
Result: Excel will automatically fill in the rest of the cells as shown below.
Note: You can use other information to be split into a new cell. You may want to try this exercise to see what it looks like.
Using Formulas to Split Cells
To split a cell into new cells, we can write formulas. For example, we are going to split full names into first and last names.
1. Write the formula in cell C3. It will return the first name as below.
2. Write the formula in cell D3. It will return the last name as below.
3. Select the range C3:D3 and, using the fill handle, drag the formula down.
| 4 of 12 finished! Recommending more on the Range: Next Example >> |
| << Previous Example | Skip to Next Chapter 03: Understanding Workbook |