In the recent past, we have gone through many articles related to Excel. At this time, we are going to discuss how to split cells in excel.
Not many people find it easy to work on excel but the features offered by excel make them learn to use excel! We can add, subtract, multiply, and can do all the basic functions on excel which we require to do in our day-to-day life.
But do you know you can even split columns in excel?? Yes, ms excel is that vast. If we try to learn each and every function offered by excel, perhaps, the whole day will pass and the list won’t end.
We can’t discuss all the functions of excel in one article but we have provided many separate tutorials targeting different features of excel, you can go through them on our website.
In the series of excel tutorials, this time, we have brought up with splitting cells feature of excel. Today, we will guide you through how to split a cell in excel and how to separate names in excel. To be more specific, we will teach you to how to separate first and last names in excel, so shall we start our guide??
How To Split Cells in Excel:
Splitting cells and columns in excel come out really useful when we have a long list of contacts with their full name but we need the info in two separate columns, one for the first name and the second for the last name.
In such situations, we can’t copy the first and last names separately from each row and column to the other, it’s so damn old school. Well, no need to get panicky. We will guide you through how to separate names in excel in easy steps.
There are multiple ways we can split cells and columns in excel. But we will walk you through the two basic methods. The first method is by using Convert Text to Columns and the second method uses formula.
#Method 1: Using “Convert Text to Columns”
In order to separate the first and last name from the full name given in one column, you need to open the excel sheet with the list of names there. This feature of excel is available in all its versions including 2003. 2007. 2010 and above. The screenshot we have provided is taken from MS office 2007.
All the steps are almost similar in all the office versions. The little difference you might find in locating the “convert text to columns”, rest of the method is the same.
Okay, now open the excel sheet containing the list of full names. Now select all the names in the ‘Full Name’ row starting from the name you want to split in the first and last name.
After making the selection, move your cursor to the top menu and select Data. Under Data, you need to go to the option Text to Columns. To locate the options easily, you can refer to the below screenshot.
As sooner you select Text to Columns, a new dialog will pop up, which looks like the below screenshot.
That’s the main window where we need to configure the settings to split the cell. In the first step of this configuration, you need to choose the file type to describe the type of data you have on your sheet. In our case, it’s Delimited so choose that and click the Next.
Now in step 2(While clicking Next), you need to select the delimiters. Since we have space between the first and last name, we need to check the option Space. You can select comma, semicolon, or other such options if these are the separators between the first and last name in your sheet.
But make sure to uncheck the rest of the options including Tab. Once all the required settings are done, click Next.
In the third step, you don’t need to do anything, just click Finish and the name would be separated into two other cells like the below screen.
Now you can rename the cell title and replace, Full Name by First Name and First Name by the Last Name and make it look like the below screen.
But if you want to have all the three columns, like Full Name, First Name, and Last Name, we have a solution for that too. We can do this in two different ways.
#1. Copy the entire Full Name column to the other column and perform the same method in the copied column. That copied column would be split into two other columns and your Full Name column would remain harmless.
#2. The second way you can do this is by selecting the destination of the split columns and row. You’ll get this option on the window where we finished the process by clicking the Finish.
Just click on the option highlighted by the arrow in the above screenshot and then click the desired column where you would like your column to be split. and click that option again. The entire process you can understand by the below screenshot.
Once done, the column would be split into two separate columns. The result will look like this.
This is just one method to split cells and columns in excel. You can do the same process by apply the formula =LEFT(A2,FIND(” “,A2,1)-1) to achieve the first name and =RIGHT(A2,LEN(A2)-FIND(” “,A2,1)) to achieve the last name.
If you are thinking that applying this formula on the excel sheet and getting the job done is difficult, then you are wrong!
Let me tell you quickly How to split cells in excel using the formula.
Suppose you have the Full Names in column A, and you want them to be split in columns B and C respectively. Now apply the formula =LEFT(A2,FIND(” “,A2,1)-1) in column B and press enter.
Now Just drag that cell to the last name in the row. Similarly, apply the formula =RIGHT(A2,LEN(A2)-FIND(” “,A2,1)) in column C and drag the cell to the last name.
That’s it. I hope you got both the methods clearly. For any queries, you can contact us by filling out the below form.
Quick Links
