Split Cells in Excel

Splitting cells in excel means splitting the data present in excel in two or more than two parts in another column, splitting is done by text to columns in excel where we have two options as delimited which uses a delimiter to split the cells and fixed-width where we define the width from we want to split the cells, text to columns can be accessed from the data tab or keyboard shortcut ALT+ A+ E.

How to Split a Cell in Excel

Sometimes you need to split cells in excel if they contain multiple sets of data that you want to analyze independently. Suppose one column contains addresses, including street addresses and Zip codes.

You can download this Split a Cell Excel Template here – Split a Cell Excel Template

You have seen most of the time while working on Excel that data is organized into columns, and each column contains specific information associated with a certain row. For example, one column might contain the customer’s name, Customer’s DOB, contact number, address, etc.

In this article, we will learn how to split cells in excel using Text to Column Method

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

Split Cells in Excel Using Text to Column method

We have given some list of names, including Full name.

Follow the below steps to split cells in excel these names into the First Name & Last Name separately.

Select the whole-cell range which you want to split. Here we have selected the range from A3:A10. Refer to the below screenshot.

Go to the DATA tab.
Click on the Text to Columns option under the Data Tools section. See the below screenshot.

It will open a Convert Text to a Column dialog box like the below screenshot.

Let us elaborate on the two ways – Delimited Option and Fixed Width.

Method #1 – Split Cells in Excel Using Delimited Option

This is the 3 step process.
Make sure the Delimited option is selected (It is by default selected here). This option allows you to separate the first name and the last name based on a specified separator. In our example, the space bar is a separator here. Refer to the below screenshot.

Click on the Next button.
The 2^nd step screen will appear.

Select the checkbox for Space as the delimiter under the Delimiters section and deselect other options here (if selected). Refer to the below screenshot.

Click on the Next button.
The 3^rd screen will appear here. In this step, you can specify the destination where you want to see the result. The default destination here in Cell A4 as shown in below screenshot.

If you continue with the default destination cell address, it will replace the original data set. If you want to keep the original dataset as it is, selects another cell as the destination.
In this example, B4 is selected.
Click on the Finish button.

The final result is shown below:

Method #2 – Split Cells in Excel Using Fixed Width Option

Suppose you want to split the text at a particular point. Use this option.

We have given the list of Date & time, and we need to split this list.

Follow the below steps:

Select the cell’s range.
Go to the DATA tab.
Click on the Text to Columns option under the Data Tools section.

It will open a dialog box to convert Text to Columns wizard. This wizard has 3 step process. Refer to the below screenshot. Select the Fixed Width option.

Click on the Next button.
It will appear on the 2^nd step screen like the below screenshot.

Under the Data Preview section, we can create a broken line and place it at the desired position on the text where we want to split cells in excel.
By double-clicking on the line, we can remove the break line too.
Now we will place the break line according to our desired results.
As in our example, we want to split cells in excel forgiven the list into two parts: Date & Time.
Click on the Next button, and it will appear in the 3^rd step process.
You can now select how to import each new column. Choose the data type under the Column data format section, as shown in the below screenshot.
Select the destination cell address where you want the results.
Click on the finish.