Text to Columns in Excel

Excel Text to Columns

Text to Columns in Excel is a method which is used to separate a text in different columns based on some delimited or any fixed width, there are two options to use text to columns in excel one is by using a delimiter where we provide delimiter as an input such as comma space or hyphen or we can use fixed defined width to separate a text in the adjacent columns.

Where to Find Text to Columns Option in Excel?

In order to access Text to Columns in Excel, go to Data, then Data Tools and Text to Columns.

Text to Columns in Excel 2

To open Text to Columns, the keyboard shortcut is – ALT + A + E.

How to Split Text to Columns in Excel? (with Examples)

You can download this Text to Columns Excel Template here – Text to Columns Excel Template

Examples #1: Split First Name and Last Name

Assume you have the list of names that includes both First Name & Last Name in a single column.

You can download this Text to Columns Excel Template here – Text to Columns Excel Template

Now you need to split the first name and last nameSplit The First Name And Last NameSplit Name in Excel refers to separating the names into two distinct columns. It splits the whole name into First Name, Last Name, and Middle Name. We can separate names using several ways such as the "Text to Column technique" and the "Formula technique.read more separately.

Text to Columns in Excel example 1

Now, we need to split the first name and the last name and get these in separate cells.

Below are the steps for splitting first name and last name in separate cells –

  1. Select the data.


    Text to Columns in Excel example 1-1

  2. Press ALT + A +E. This will open the Convert Excel Text to Columns Wizard.


    Text to Columns in Excel example 1-2

  3. Now, make sure Delimited is selected and click on Next.


    Text to Columns in Excel example 1-3

  4. In the next step, first uncheck TAB and select SPACE as the delimiter. If you think that there are double/triple consecutive spaces between the names, then select the ‘Treat consecutive delimiters as one’ option. Click on Next.


    Text to Columns in Excel example 1-4

  5. In Step 5, select the destination cell. If you do not select a destination cell, it will overwrite your existing data set with the first name in the first column and last name in the adjacent column. If you want to keep the original data intact, either create a copy or choose a different destination cell.


    Text to Columns in Excel example 1-5

  6. Click on FINISH. That will split the first name and last name separately.


    Text to Columns in Excel example 1-6

    Note: This technique is ideal only for first name and last name. If there are initials and the middle names, you need to use a different technique.

Examples 2: Convert Single Column Data into Multiple Columns

Let us 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.

The below data is in one column, and you need to convert it into 4 columns based on the heading.

Text to Columns in Excel example 2

From the above data, we can understand that there are four pieces of information in a single cell, i.e., Hex No., Description. Hex No., Description. Now we will apply the “Fixed width” method of Text to Columns.

  • Step 1: Select the data range.
Text to Columns in Excel example 2-1
  • Step 2: Go to Data and select the Text to Column excel option (ALT + A + E). This would open up the Text to Column Wizard window.
Text to Columns in Excel example 2-2
  • Step 3: Select the Fixed Width option and click on Next.
Text to Columns in Excel example 2-2
  • Step 4: 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.
Text to Columns in Excel example 2-3
  • Step 5: Click on the next option, and in the next option, select the destination cell as B1. This would insert the data in the new column so that we will have our original data as it is.
Text to Columns in Excel example 2-5
  • Step 6: Now, click on the Finish button, and this would instantly split data into 4 columns, i.e., Hex, Description, Hex, and Description, starting from column B to column E.
Text to Columns in Excel example 2-6

Examples 3: Convert Date to Text Using Text to Column Option

If you do not like formulas to convert the date to text format, you can use TEXT TO COLUMN Excel OPTION. Assume you have data from cells A2 to A8.

Text to Columns in Excel example 3

Now you need to convert it into text format.

  • Step 1: Select the entire column you want to convert.
Text to Columns in Excel example 3-1
  • Step 2: Go to Data and Text to Columns
Text to Columns in Excel example 3-2
  • Step 3: Make sure delimited is selected and click on the Next button.
  • Step 4: Now, the below pop up will open and uncheck all the boxes and click the Next button.
Text to Columns in Excel example 3-3
  • Step 5: Select the TEXT option from the next dialogue box. Mention the destination cell as B2 and click finish.
Text to Columns in Excel example 3-4
  • Step 6: Now, it instantly converts it into text format.
Text to Columns in Excel example 3-5

Examples 4: Extract First 9 Characters from the list

For example, in the data set shown below, the first 9 characters are unique to a product lineProduct LineProduct Line refers to the collection of related products that are marketed under a single brand, which may be the flagship brand for the concerned company. Typically, companies extend their product offerings by adding new variants to the existing products with the expectation that the existing consumers will buy products from the brands that they are already purchasing.read more.

Text to Columns in Excel example 4
  • Step 1: Select the data range.
Text to Columns in Excel example 4-1
  • Step 2: Press ALT + A + E and select the Fixed width, and click on Next.
Text to Columns in Excel example 4-2
  • Step 3: Now put a delimiter exactly after the 9th character, as I have shown in the below image.
Text to Columns in Excel example 4-3
  • Step 4: Click on next and select the destination cell as B2.
Text to Columns in Excel example 4-4
  • Step 5: Click on Finish, and this will extract the first 9 characters from the list in column B and the remaining characters in column C.
Text to Columns in Excel example 4-5
Text to Columns in Excel example 4-6
Text to Columns in Excel example 4-7

Recommended Articles

This has been a guide to Text to Columns in Excel. Here we discuss where to Convert Text to Columns Excel along with the example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>