Text To Column in Excel | Split Cells

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:

How to Apply Text to Column to Split Cells

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

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:

How to Split Cells in Excel using Text to 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.

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.

How to separate text into Multiple column using Text to column

  1. Click on home
  2. Select Range of Cells
  3. Click on Text to Columns

    Go-to-Data-Text-to-Columns

  4. Choose the Delimited option & Click Next
  5. Click on Comma button then next button.
  6. Choose Destination cell & 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.

Split Cells into Multiple Columns.

Let’s see how to split the data into multiple columns. This is also part of data cleaning. Sometimes your data are in one single column, and you need to divide it into multiple adjacent columns for applying Sort, Filter or Pivot table.

All the information is in one single column, but you need to separate it. In our earlier example, we have applied “Delimited” technique. However, this time, we will apply “Fixed width” strategy of Text to Columns.

Transfer Single Columnar Data into Multiple Columns using Text to Columns
Observation:

From the above data, you can understand that there are four pieces of information in a single cell i.e. Account No., Item No., Check No., and Description.

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

Our aim is to separate that one column in four different columns. Let’s see how its done:

Step 1: Select your data to the range (from the first data cell). Go to Data tab, and then go to Text to Columns. On the “Convert Text to Columns Wizard – Step 1 of 3” box, choose Fixed Width option. Click Next.

Transfer Single Columnar Data into Multiple Columns using Text to Columns

Step 2: You will see the fixed width divider vertical line marks (called Break line) in the Data Preview window. You may need to adjust it as per your data structure.

  • On double click, the brake line will be deleted
  • When you click once, a new break line will be created at the point of click
  • If you click an existing break line and drag it, it can be moved to the desired position

You will see the fixed width divider vertical line marks you may need to adjust it as per your data structure - Convert Text to Columns wizard

  • After placing appropriate break lines, click Next.

Step 3: As you click on next, you will reach Step 3 of 3 of Text to Columns wizard. You may change the destination cell so that your original data remains intact and output appears in adjoining columns’ cells.

Important Note:

If you click Finish, you will observe that the 3rd column of the output has last the prefix zeroes i.e. 00816530 gets converted to 816530, thereby corrupting the data.

Step 4: To retain the prefix zeroes, you should have chosen the column from the Data Preview window of Step 3 of 3 of Text to Columns wizard. It will blacken out the column as shown in the picture below.

Select particular column and Select Number stored as Text while using Text to Columns

Step 5: Once the column is blackened out, choose “Text” option from the list of options [General, Text, Date and Skip]. Now if you click on Finish, you will see the zeroes are retained in the final output columns.

Solution-of-multiple-columns-

We have solved another interesting query of quite a few people, in case if you wish to convert an invalid date format into a valid date format. Click here:

4 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

Related Tutorials

Connect to Data in Power Query
February 21, 2019
How to use Text to Column in Excel with 4 Difference Example
February 21, 2019
Things you should learn in Excel Course
February 13, 2019
How to use Clean Function in Excel
February 11, 2019
How to use the Excel Choose Function
February 11, 2019
How to use the Excel Code Function
February 11, 2019
How to use the Excel Column Function
February 11, 2019
How to use Ceiling Function in Excel
January 30, 2019
How to use Char Function in Excel
January 28, 2019
Pivot Tables (Complete Guidelines)
December 27, 2018
Power Bi Dax Deduplication Based On Column
December 17, 2018
Special Character Symbol List with Shortcodes in Excel
December 15, 2018
How to use AVERAGEIFS FUNCTION in Excel
December 10, 2018
How to use AVERAGEIF FUNCTION
December 10, 2018
How to use ABS Function in Excel
December 10, 2018
How to use AND Function in Excel
December 10, 2018
Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas and Functions in PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018