Splitting text of one cell in multiple columns



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/


19889 Views 11 Likes Comment   Share Students   Report


About the Author

Trainer

/coaching/excel.asp

Comments :

Related Articles


Loading


Popular Articles





CCI Pro

CCI Articles

submit article


Company
Featured 29 October 2026
Accounts Executive

Synergy Keystone

Chennai

B.Com

View Details
Company
12 November 2026
Auditor

Oz Taxation

Vijayawada

Graduate (Any)

View Details
Company
Featured ARTICLESHIP 31 October 2026
CA Articleship

apricus india

Navi Mumbai

CA Inter

View Details
Company
13 November 2026
Chartered Accountant - Fresher

Neeraj Bhagat & Co

New Delhi

CA

View Details
Company
Featured 07 November 2026
Paid Assisstant

DPV & Associates

Chennai

CA Inter

View Details
Company
14 November 2026
Manager - Indirect Tax (GST)

Westbridge Consulting LLP

New Delhi

CA

View Details
Company
31 October 2026
Accountants

sankaran and krishnan

New Delhi

B.Com

View Details
Company
ARTICLESHIP 18 November 2026
Articled Assistant

A.Singhi & Co

Kolkata

CA Inter

View Details