fbpx
Yodalearning>Tutorials>How to Split Text to Columns in Excel

Excel Text to Columns or Split Cells in Excel is used in data cleaning and data re-arrangement. Excel text to columns formula technique is used for following:

8 Excel Challenges to Know How good you are in excel?  Accept the Challenge

How to Split Text into Multiple Column?

Split the text as last name and first name in two different columns.

If a cell has got more than just a word for e.g.: First name and surname i.e. the last name. Then, select the cell, range, and the cell containing the text values that you wish to split. You have a long of list of full names as shown below (Example: AbduSalaam, Ismael). You need to split the text into last name and first name in two different columns.

Solution:

You can apply Text to Column technique or split cell technique. It cuts the data into different columns. 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 given example, it is clear that full name is in a single column. Separate into two columns. One is the last name, and other is the first name.

Before

Split the Data in to two Column with help of Text to Column

After

Solution of Text to Columns

Recommended Courses

Learn how to do text to column or split text in multiple column or you can learn such more tricks. Learn More

Create Dynamic Graphs & Excel Presentation. Also, Learn how to create Data Analytic Chart & Graph. Learn More

Testimonial by spreadsheet guru

Text to Columns

To separate the contents of one Excel cell into separate columns, you can use the ‘Convert Text to Columns Wizard’. For example, when you want to separate a list of full names into last and first names.

To separate fixed-width text into multiple columns, follow these steps:

  1. Select Range of Cells
  2. Choose the dataset and go to “Data” tab
  3. Click on Text to Columns

    Go-to-Data-Text-to-Columns
  4. Choose the Delimited option.
  5. Click On Next button.
  6. Click on Comma button then next button.
  7. Choose Destination cell
  8. Finish.

Text-to-Columns-Wizard-Step-3

Result: By clicking on the finish, you will see all the surnames in one column, and the name is another column. This is the process of Text to columns in Excel

Solution of Text to Columns

We have solved another interesting query of excel users, in case if you wish to convert an invalid date format into a valid date format.

Learn Remove Password From Excel and Using Excel VBA (Coding) or you can learn such more tricks. Learn More

Create Dynamic Graphs & Excel Presentation. Also, Learn how to create Data Analytic Chart & Graph. Learn More

You can also use text to a column in excel VBA, to get Excel VBA tutorial you need to enroll in our course

Also, now you can, Transfer Single Columnar Data into Multiple Columns.  Click here

Now you can convert or fix digits with trailing minus into negative digits. Check out how by clicking here:

Split Cells In Excel | Text To Columns | Excel Split Column into multiple columns | Multiple columns Split into excel tutorials

Related Items:

3 Responses
  1. Bhakti

    It really helped me to split names in two different cells. Got to know how to use text to column function in Excel.
    Thanks Yodalearning.

Leave a Reply