Split Cell in Excel (Table of Contents)
Split Cell in Excel
There were situations I dealt with where I had copied the data from text file to excel and found everything in a single cell itself. Below screenshot is one such instance where I wanted each heading to be showed in a separate column but ended up getting the data in a single cell itself.
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 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 TEXT TO COLUMN tool. Text to Column splits the single cell values into multiple cells by finding the single common element.
How to use Split Cell in Excel
Example of this article includes telecom records data.
Example #1
This data includes a call made date, time, duration, number, and cost involved to make 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 into 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 Other option here. Here we need to mention on what basis we are splitting the cell. If you observe the data, after every heading we have straight slash sign (|). So I will tell the 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 a 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 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 quickly upgrade your MS Excel version to 2013 or 2016. Flash Fill is the option which 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 B2 cell.
Now go to Data tab and click on 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.
Note: Even though flash fill is easy it may go wrong in many cases. So be careful while using it.
Now we have learned how to split a cell into multiple columns. Now download the attachment, go to Home Worksheet and try to find the common element in that cell values and split the cell into multiple columns.
Things to Remember about Split Cell in Excel
- Ctrl + E is the shortcut key for FLASH FILL.
- Try to find the common element in the data pattern and split the cells.
- Using this option we can convert the test series date into actual dates as well.
- Semicolon, Space, Tab, Comma are some of the common elements in the data series.
Recommended Articles
This has been a guide to Split Cell in Excel. Here we discuss how to use the Split Cell in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
