Let's say you have a long list of full names as shown below (Example: AbduSalaam, Ismael). You need to split the text as last name and first name in two different columns.
Solution:
You can apply Text to Column technique. It cuts the data into different columns. You need to specify the column delimiter i.e. the character where the data must be split. In the example given below, the delimiter is a comma, which exists between last name and first name.
In the above example, it is clear that full name is in a single column. It needs to be separated into two columns. One is the last name, and other is the first name.
1st Step: Choose the dataset and go to “Data” tab
2nd Step: Then click on Text to Columns
3rd Step: As you click on Text to Columns, you will get Text to Column Wizard box. The box indicates that you are in Step 1 of 3. In the first step, we will choose the Delimited option. Then click on Next button.
Step 4: In the next step, you need to switch on the Comma checkbox. As soon as you select comma checkbox, the single column appears to split into two columns (refer Data Preview box).
Step 5: Click on Next button to go to Step 3 of 3 of Text to Column Wizard. Here you need to choose the Destination cell. This will be the cell, wherein data after getting split will appear. In our example, the original list of names is in Column E., so I choose the very next of column’s adjoining cell. This way our original data remains intact. Then click on Finish.
Result:
By clicking on the finish, you will see all the surnames in one column, and the name in another column.
Recommended course to learn MS Office tools like Advanced Excel, Charts, PowerPoint Ninja, VBA Macros and Word for Finance Professionals.
Click here: /coaching/584-ms-office-bundle.asp
Canonical link: http://blog.yodalearning.com/2015/06/19/tricks-of-text-to-columns-in-ms-excel/
CAclubindia
