Excel Example – Split Cells in Excel

Date:

Share post:

To split cells in Excel, add a new column, change the column widths and merge cells. To split the contents of a cell into multiple cells, use the Text to Columns wizard, flash fill or formulas.

Split a Cell

Use the following trick to “split” a cell in Excel.

1. For example, task B starts at 13:00 and requires 2 hours to complete.

Split Cell B3

Suppose task B starts at 13:30. We would like to split cell B3 and color the right half.

2. Select column C.

Select Column

3. Right click, and then click Insert.

Click Insert

Result:

Inserted Column

4. The default width of a column is 64 pixels. Change the width of column B and C to 32 pixels.

Change Column Widths

5. Select cell B1 and cell C1.

6. On the Home tab, in the Alignment group, click the down arrow next to Merge & Center and click Merge Cells.

Merge Cells

Result:

Merged Cells

7. Repeat steps 5-6 for cell B2 and cell C2 (and cell B4 and cell C4).

8. Change the background color of cell B3 to No Fill.

Result:

Split Cell

Note: suppose task A ends at 15:30. Use the trick explained above to “split” cell E2. Download the Excel file and give it a try.

Text to Columns

To split the contents of a cell into multiple cells, use the Text to Columns wizard. For example, let’s split full names into last and first names.

1. Select the range with full names.

Text to Columns Example

2. On the Data tab, in the Data Tools group, click Text to Columns.

Click Text to Columns

The following dialog box appears.

3. Choose Delimited and click Next.

Convert Text to Columns Wizard - Step 1

4. Clear all the check boxes under Delimiters except for the Comma and Space check box.

See also  Excel Example - Transpose in Excel

5. Click Finish.

Convert Text to Columns Wizard - Step 2

Result:

Split Full Names

Note: this example has commas and spaces as delimiters. You may have other delimiters in your data. Experiment by checking and unchecking the different check boxes. You get a live preview of how your data will be separated.

Flash Fill

Do you like Magic? Instead of using the Text to Columns wizard, use flash fill to quickly split data into multiple columns.

1. First, split the contents of one cell into multiple cells.

Flash Fill Example

2. Select cell B1 and press CTRL + E (flash fill shortcut).

Split Data Into Multiple Columns

3. Select cell C1 and press CTRL + E.

Split Data Into Multiple Columns

4. Select cell D1 and press CTRL + E.

Split Data Into Multiple Columns

Note: flash fill in Excel only works when it recognizes a pattern. Download the Excel file and give it a try. Visit our page about Flash Fill to learn more about this great Excel tool.

Formulas to Split Cells

One drawback when using these tools is that the output will not automatically update when the source data changes. Create formulas to overcome this limitation. Let’s split full names into first and last names.

1. The formula below returns the first name.

First Name

2. The formula below returns the last name.

Last Name

3. Select the range B2:C2 and drag it down.

Formulas to Split Cells

Note: visit our page about separating strings to understand the logic behind these formulas.

4. If you have Excel 365, use the new TEXTSPLIT function to split text into rows or columns using delimiters.

TEXTSPLIT function

Note: the TEXTSPLIT function, entered into cell B2, fills multiple cells (B2 and C2). Wow! This behavior is called spilling.

Additional Tips:

  • If you have multiple delimiters, use the “Treat consecutive delimiters as one” option in the Text to Columns wizard.
  • Adjust column widths to accommodate the split data.
  • Consider using conditional formatting to highlight specific parts of the split data.
See also  Excel Example - AutoFit in Excel

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Related articles

Excel Example – Subtract in Excel

Subtract There's no SUBTRACT function in Excel. However, there are several ways to subtract numbers in Excel. Are you...

Excel Example – Most Used Functions in Excel

Most Used Functions Let's check out the 10 most used Excel functions. Visit our section about functions for detailed...

Excel Example – ROW function in Excel

The ROW function in Excel returns the row number of a reference. For example, =ROW(C4) returns 4. The...

Excel Example – Move Columns in Excel

To move columns in Excel, use the shift key or use Insert Cut Cells. You can also change...