Excel Split Cell

Split Cell in Excel (Table of Contents)

• Introduction to Split Cell in Excel
• Examples of Split Cell in Excel
• Split Cell in Excel Using FLASH FILL Option

Introduction to Split Cell in Excel

Split A Cell in Excel, also known as Text To Columns and sometimes called as delimit, is used to split the data of a cell. We can spit a cell with different parameters such as Space, Blank, Commas or any other criteria which breaks a cell into 2 or more cells. This can also be done using short cut keys ALT + A + E simultaneously once we select the data. We can also split a cell with parallel fixed width if we want a specific length to break.

Are the above looks ugly isn’t it? Yes, it looks ugly. But that is how the data structure; if you are copying the data from text from, we have to deal with it. Often times, when we are working with an excel sheet, data is well organized into each column separately and each column represents one heading respectively like the below image.

The image looks a beauty. In excel, we can split the cells into multiple columns by using the TEXT TO COLUMN tool. Text to Column splits the single-cell values into multiple cells by finding the single common element.

Examples of Split Cell in Excel

An example of this article includes telecom records data.

Example #1

This data includes a call made of date, time, duration, number, and cost involved in making this call. I copied the data from one of the web sources, and it looks like this when pasted into the Excel worksheet.

Now I cannot do anything with this data because all the values are located in one cell in the respective row. By using Text to Column, we can split the single-cell data. Follow the below steps to split the cell.

Step 1: Select the data range.

Step 2: Go to Data > Text to Columns (Shortcut to open this is ALT + A + E)

Step 3: After clicking on this option, this will open the below dialogue box. Select Delimited here and click on NEXT.

Step 4: Select the Other option here. Here we need to mention on what basis we are splitting the cell. After every heading, we have a straight slash sign (|) if you observe the data. So I will tell excel that I want the separator a straight slash (|) sign and click on NEXT to continue.

Step 5: Now select the result destination cell under the Destination section. Since I want the resulted data to be displayed in the same cell, I have selected A1 as the starting cell and now click on FINISH.

Step 6: It will split the cell values and the separator sign, which is a straight slash (|).

Wow!!!! The data looks a lot better now. TEXT to COLUMNS saved me here.

Example #2

I have First Name & Last Name data in the single-cell itself.

Since both first name & last name is there in the single cell, I want to split the first name separately and the last name separately.

In our last example, the common element was a straight slash (|) sign; in this data common element is Space after the first name. So I will split the cell based on space here.

Step 1: Select the data.

Step 2: Go to Data > Text To Columns

Step 3: Select Delimited and click on NEXT

Step 4: In this section, select the basis of separation. The basis is SPACE, so select the space check box.

Once SPACE is selected in the preview section, I can already see how the final data pans out; now, click on NEXT.

Step 5: Select the destination cell as A1 and click on FINISH.

Step 6: We will get the first name & last name separately in two columns.

Step 7 – Since there was a space in First Name & Last Name, it has separated both of them. Manually correct the headings.

Split Cell in Excel Using FLASH FILL Option

If you are using Excel 2010 and earlier versions, you need to upgrade your MS Excel version to 2013 or 2016 quickly. Flash Fill is the option that is introduced in the 2013 version. It is very useful to fill the pattern of data from other cells. It will grasp the pattern of our data and extract the data for us.

For example, take a look at the previous example data.

Now type the first name in the B2 cell.

Now go to the Data tab and click on the Flash Fill option or press the shortcut key Ctrl + E.

Once clicked on this option, it will separate all the First names from the list.

Do the same thing for the last name as well.