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)

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 Name Split 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.

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 –

Select the data.
Press ALT + A +E. This will open the Convert Excel Text to Columns Wizard.
Now, make sure Delimited is selected and click on Next.
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.
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.
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.