Last updated on May 13th, 2018
If you want to extract the information from one cell into two or multiple cells splitting the cell is required. Splitting cells also provide better sorting and filtering of an existing table. In excel, there are many ways to split cells. Some of the processes are Unmerge cells, Flash Fill, and Text to Column feature. You can also use formulas or VBA code to split cells. In this article, we will see the different processes on how to split cells in excel.
Table of Contents
- Splitting Cells by Using the Unmerge Cells Option in Excel
- How to Split a Single Cell Diagonally in Excel
- How to Use Text to Columns Feature to Split Cells in Excel
- How to Separate the Content of a Cell in Excel with Flash Fill
- How to Split Cells in Excel Using Formula
- VBA Code to Split the Content of a Cell
- Read More…
Splitting Cells by Using the Unmerge Cells Option in Excel
While working on an imported excel file, you may find that some of the cells of your imported spreadsheet are merged together. You may not require the merged cells. There is an easy way to unmerged/split these cells. All you can do is select the merged cells together and click on the Unmerge Cells option on top.
Read More: How to Delete Blank Rows in Excel (6 Ways)
How to Split a Single Cell Diagonally in Excel
For splitting a single cell diagonally, you need to get the help of format cells option. Suppose you have a single cell where two words are written. The words are “Up” and “Down”. You wish to split that two words diagonally in a single cell. To begin the operation, 1st select that targeted cell and click on the right button of your mouse to select Format Cells option. Under the Format Cells dialogue box, click on to the Border option and then select the diagonal border and press OK.
You will see a diagonal line has appeared. Now for adjusting the positions of the words, select the first word “Up” and click on the drop-down option of Font under the Home tab. A new dialogue box of Format Cells option will come up which will only highlight the fonts. Select the Superscript option under Effect and choose the font size and style you want and then press OK.
Following the same procedure select the second word and under the Effect, bar click on the Subscript option and then press OK.
Now for aligning the words perfectly put some extra space between your words. By this, you will get to see the words aligned diagonally in a single cell. This is how you can split a single cell diagonally.
Let’s do the same thing briefly by inserting a box for a better view. Before doing the same steps above, make the height and width of the targeted cell larger by dragging it and set the alignment of the 1st word on top. Now insert a triangle shaped object in that box by choosing the Shapes option under the Insert tab while pressing the Alt on the keyboard. It will lock down the object in your cell so that by dragging up or down the cell the alignment of that object is not disturbed. Rotate the object just the way you like and write whatever you want and align it to right. You will see that the two words are split diagonally. The word “Up” is the upper side of the cell and the word “Down” is in the lower right corner of the cell inside the box.
How to Use Text to Columns Feature to Split Cells in Excel
The feature of Text to Columns in excel allows text strings to be split by some delimiter, which are the semicolon, comma, space or other characters. Suppose we have a data table where the participant column has information about different candidates. We want to split this column into three where the new columns will be the First & Last name, Age, and Country. The whole procedure is given below.
- Make three new columns titled as Last name, Age, and Country.
- Choose the cells you want to separate under the Data option on top and then click on to Text to Columns.
- A dialogue box of Convert Text to Columns wizard will come up. In the 1st step click on to Delimited as the file type and press Next.
- After that, specify the Delimiters and text qualifier if needed. Here we choose Tab, comma, and space as Delimiters.
- In next step, you will select the range to split the data. Beside the Destination box press the arrow icon to define the range. Then press Finish.
You will find the exact result that you wanted. In 1st column, write Frist Name instead of Candidate information. Set the alignment by your choice for a good view.
Note: You can also fix the width of each column in step 1 of Convert Text to Columns wizard box. If your table contains any dates, you can also apply the date format of your choice in step 3.
Read More: How to Compare Two Columns or Lists in Excel
How to Separate the Content of a Cell in Excel with Flash Fill
If you are using the latest version of Microsoft Excel, you can be benefitted with the Flash Fill option. The Flash Fill option in excel was introduced from 2013 version. I am using the Excel 2016 version. Using the Flash Fill option in 2016 version is like performing magic. To perform flash fill you need to create some extra columns beside your main column.
In your example, our main column consists of a candidate`s name, age, and country. We will split this column into 4 different columns to show the First & Last name, Age, and Country. It is mandatory to have the main column along with the other columns. After inserting data to the whole table, you can delete the main table. The procedure is stated below.
- 1st create 4 new columns beside the main column.
- In the cell, B2 write Albert and press enter. After this go to the Data option on top and click on to the Flash Fill indicating cell B2. You will see that all the first name is inserted automatically in the adjacent column.
- Carry on this process for the other columns. Like for the Column Age, press 62 in column D2 and perform the Flash Fill.
Note: Don`t delete or edit the main column before performing the flash fill. You may find that same name is copied every time you perform the flash fill. In that case, click on the Refresh All under the Data option of excel and then repeat the operation again.
How to Split Cells in Excel Using Formula
In Excel, we can use different functions like LEFT, RIGHT, MID, LEN and SEARCH to split the content of a cell. Together these functions make formulas which can be used to extract content from a cell and it can also be used in other cells. In this example, I used some formulas to extract First & Last Name, Full Name and Age from a specific column and I used carried out the result in different columns. The procedure is given below.
- 1st create 4 new columns beside your main column.
- In cell B2, write down the formula
=LEFT(A2, SEARCH(" ",A2,1)-1)
and press Enter. Drag down the formulated cell to copy the formula for the rest of the cell of column B (First Name). You will see that it will show the first name only just like the below picture.
- To carry out the last name we will follow the same procedure, only the formula will be changed. In cell C2, write down the formula,
=MID(A2, SEARCH(" ",A2) + 1, SEARCH(",",A2,SEARCH(" ",A2)+1) - SEARCH(" ",A2) - 1)
and drag down the formula to the rest of the cell of column C.
- Write the formula,
=LEFT(A2, SEARCH(",", A2)-1)
in cell D2 and drag down the formulated cell to show full name.
- Finally, in cell E2 write the formula,
pressing enter drag down the formulated cell to copy this formula for the rest of the cells of column E. It will show the age of the candidate from column A.
VBA Code to Split the Content of a Cell
There are many codes available to split cells in excel. In this example, we used a simple code which split the content of a cell into 3 cells. We will see the Name, Age and Country name which are split using this code. The code is given below which was found out from this link https://superuser.com/questions/971293/need-to-split-a-line-of-text-in-an-editable-cell-deliminated-by-signs/971314#971314
Sub NameTest() Dim txt As String Dim i As Integer Dim FuLLName As Variant txt = ActiveCell.Value FuLLName = Split(txt, ",") For i = 0 To UBound(FuLLName) Cells(1, i + 2).Value = FuLLName(i) Next i End Sub
The following picture shows the result of the code.
In this article, we can see different methods of splitting cells in excel. Among all of them, using the Text to Column feature is the best option as you can eliminate different characters and you can also have a preview of the splitting. Flash Fill can also be used but some error may occur while performing this feature.
Hope this article may help you. Leave a comment below and let me know if you know any other approach of splitting cells in excel.