Split Cells in Excel

Publication Date :

Blog Author :

Edited by :

Reviewed by :

Download FREE Split Cells Excel Template and Follow Along!
Split Cells Excel Template.xlsx

Table Of Contents

arrow

How to Split a Cell in Excel?

Let us consider some examples to understand the splitting of cells with the help of the text to columns wizard.

Example #1–Split by “Delimited” Option

The following table shows the full names of seven people. We want to split the first and the last name into separate columns. Use the “delimited” option of the text to columns wizard.

Split Cells in Excel Example 1

The steps to split cells in excel with the help of the delimiter character are listed as follows:

  1. Select the cell range A3:A10, which is to be split. The same is shown in the following image.


    Split Cells in Excel Example 1-1

  2. In the Data tab, click the “text to columns” option under the “data tools” group.


    Split Cells in Excel Example 1-2

  3. The “convert text to columns wizard” dialog box appears, as shown in the following image.


    Split Cells in Excel Example 1-3

  4. Choose the “delimited” option, which is selected by default. This option helps separate the data strings based on a particular delimiter character. Click “next.”


    using Delimited option step 1

  5. Under “delimiters,” select the checkbox for space. Deselect the other delimiters (if selected), as shown in the following image. Click “next.”


    Convert Text To Columns Wizard - Step 2 of 3

  6. Under “destination,” specify the cell in which the output is required. Enter “$B$4” and click “finish.”


    Note: If you proceed with the default cell address under “destination,” the output will replace the original dataset. To retain the initial data as is, select a cell to its right as the “destination.”

    using Delimited option step 1-3

  7. The output is shown in the following image. The names of column A have been split into the first name (column B) and the last name (column C).


    Note: The results of the “text to columns” property are static. This means that any change made to the source data is not reflected in the results. Hence, to include the changes, the whole process has to be repeated.

    using Delimited option step 1-4

Example #2–Split by “Fixed Width” Option

The following list shows the date and time of specific days. We want to split the date and time into separate columns. Use the “fixed width” option of the text to columns wizard.

Using Fixed Width Step 1

Step 1: Select the range A16:A20, as shown in the following image. In the Data tab, click “text to columns” under the “data tools” group.

Using Fixed Width Step 1-1

Step 2: The “convert text to columns wizard” dialog box appears. Select the option “fixed width,” as shown in the following image. Click “next.”

Using Fixed Width Step 1-3

Step 3: Under “data preview,” place a break line (on the text) at the position where splitting is to be carried out.

Since we want to split the date and time, we insert the break line between these two data strings. Click “next.”

Note: To remove the break line, double-click on it.

Using Fixed Width Step 1-4

Step 4: Under “column data format,” select date, as shown in the following image. In “destination,” enter the cell address where the results are required. Click “finish.”

Using Fixed Width Step 1-5

Step 5: The output is shown in the following image. The data strings of column A have been split into the dates (column B) and the time (column C).

Using Fixed Width Step 1-6

Frequently Asked Questions (FAQs)

1

What is the splitting of cells and how is it done in Excel?

Arrow down filled
2

How to split a cell’s content separated by commas into multiple cells of Excel?

Arrow down filled
3

How to split cells in Excel automatically?

Arrow down filled