Split Cells in Excel | Using Text to Column Tool (Delimited & Fixed Width)

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 which you want to analyze independently. Suppose one column contains address 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 contain specific information associated with a certain row. For example, one column might contain the customer’s name, Customer’s DOB, their 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 below steps to split cell in excel these names into the First Name & Last Name separately.

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

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

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

Let us elaborate 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 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 below screenshot.

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

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

Click on 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 is 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 Finish button.

The final result is shown below:

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

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

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

Follow below steps:

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

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

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

Under Data Preview section, we can create a break line and place it at the desired position on the text where we want to split cell 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 cell in excel for given the list into two parts: Date & Time.
Click on Next button and it will appear the 3^rd step process.
You can now select how to import each new column. Choose the data type under Column data format section as shown in below screenshot.
Select the destination cell address where you want the results.
Click on finish.