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.

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

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

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.

Now you need to split the first name and last name separately.

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

• Step 1: Select the data.

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

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

• Step 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.

• Step 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.

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

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.

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.

• 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.

• Step 3: Select the Fixed Width option and click on Next.

• 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.

• 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.

• 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.

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.

Now you need to convert it into text format.

• Step 1: Select the entire column you want to convert.

• Step 2: Go to Data and Text to Columns

• 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.

• Step 5: Select the TEXT option from the next dialogue box. Mention the destination cell as B2 and click finish.

• Step 6: Now, it instantly converts it into text format.

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 line.

• Step 1: Select the data range.

• Step 2: Press ALT + A + E and select the Fixed width, and click on Next.

• Step 3: Now put a delimiter exactly after the 9^th character, as I have shown in the below image.

• Step 4: Click on next and select the destination cell as B2.

• 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.

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 –

• Add Text in Excel Formula
• Convert Text to Number in Excel
• Date to Text in Excel
• Numbers to Text in Excel
• Column Function in Excel