Excel Split Cell – Half, Rows, Cells, Diagonally – [Mac, Windows]

When you receive data from any database or you have copied data set from the internet then there may be a situation occurs to split cells. Like you received a data set having the full name and you want to split the full name into the first name and last name to analyze them separately. Then you require a split function in excel. Check, how to use excel split cell function to split rows, cell, and content in half.

Excel Split Cell

To split the cell in half, there are many ways that you can use to split content.

Text to Column

Excel function

Flash fill

How to Split Content into 2 cells [Text to Column]

• Choose the cell whose content you want to split.
• In the ribbon, you will find the data tab.
• Go to the data tab, and then choose text to columns.
• After clicking on this you will find a pop up of “Convert text to columns wizard”.

• Choose Delimited if it is not selected.
• Click on Next icon.
• Select the delimiters by defining the places wherever you want to perform splitting.

• Define the places where you want to split the content.
• Click on Finish icon.

How to separate text and numbers in excel [Excel Function]

When you want to slice and dice the text strings, you need to use excel text functions. For example, if you have a list of student where you want to split the name of the person into two. For this, you will be using a formula:

• Check the list of students containing full student name in column 1 whereas in column 2 you want only the first name of the student.
• In column 2, you need to apply a formula for extracting the first name

=LEFT(A2,SEARCH(“ “,A2)-1)

The formula uses the search function to get the position of the character whereas by using the left function you can extract characters from the left side.

• If you want the last name in the column, then use the Right function to get the last name using formula

=RIGHT(A2,LEN(A2)-SEARCH(“ “,A2)

How to use Excel Split Cell using Flash Fill?

Flash fill makes the patterns in the dataset and replicates this pattern. You have a set of address out of which you want to separate the city. To get the list of city, you need to enter the name of the city along with address and using an auto-fill function you can fill the cells.

Apart from all these, you can separate out the data by providing white space between data set. With the help of a tab, you can separate data in every column. You can use 3 spaces or 4 spaces to separate out each item.

Similarly like excel split cell, you can merge cells also. To merge cells in excel, you need to follow a step-by-step process that you can check by clicking on the link how to merge cells in excel. In fact, there are other options also available that you can use for the shrink to fit and wrapping of text.

Subscribe to Notifications