Excel Text to Columns

Where to Find Text to Columns Option in Excel?

To access text to columns in Excel, go to the Data tab, then Data Tools and Text to Columns.

The keyboard shortcut to open text to columns 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 a list of names that includes both first and last names in a single column.

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.

We need to split the first and last names and get these in separate cells.

Examples 2: Convert Single Column Data into Multiple Columns

Let us see how to split the data into multiple columns. It is also part of data cleaning. Sometimes your data is in one single column. You need to divide it into multiple adjacent columns.

  • Select the data. Then, press “ALT + A +E.” It will open the “Convert Text to Columns Wizard.” Now, make sure “Delimited” is selected and click on “Next.” In the next step, uncheck “TAB” and select “SPACE” as the delimiter. If you think of double/triple consecutive spaces between the names, choose the “Treat consecutive delimiters as one” option. Finally, click on “Next.” Select the destination cell. If you do not select a destination cell, it will overwrite the existing data set with the first name in the first column and the last name in the adjacent column. If you want to keep the original data intact, 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 names and last names. You need to use a different approach if there are initials and middle names.

Note: This technique is ideal only for first names and last names. You need to use a different approach if there are initials and middle names.

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: “Hex No., Description,” “Hex No., Description.” Therefore, we will apply the “fixed-width” text to the columns method.

  • Step 1: Select the data range.

  • Step 2: Go to the “Data” tab and select the “Text to Column” Excel option (ALT + A + E). It would open up the “Convert Text to Columns Wizard” window.

  • Step 3: Select the “Fixed width” option and click on “Next.”

  • Step 4: 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 select the destination cell as B1 in the next option. It would insert the data in the new column to have our original data.

  • Step 6: Now, click on the “Finish” button. It would instantly split data into four columns: “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. For example, 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 the Data tab and Text to Columns.

  • Step 3: Make sure Delimited is selected and click on the “Next” button.Step 4: The below pop-up will open, uncheck all the boxes, and click the “Next” button.

  • Step 5: Select the TEXT option from the next dialog box. Mention the destination cell as B2 and click “Finish.”

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

Examples 4: Extract the 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.

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

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

  • Step 4: Click “Next” and select the destination cell as B2.

  • Step 5: Click on “Finish.” It will extract the first 9 characters from the list in column B and the remaining characters in column C.

This article has been a guide to Text to Columns in Excel. Here, we discuss where to convert text to columns in Excel, along with the example and downloadable Excel templates. You may also look at these useful functions in Excel: –

  • Add Text in Excel FormulaAdd Text In Excel FormulaText in Excel Formula allows us to add text values to using the CONCATENATE function or the ampersand (&) symbol.read moreConvert Text to Number in ExcelConvert Text To Number In ExcelTo convert text to numbers in Excel, first select the data, then click the error handle box, and finally select convert to numbers option. Similarly, we can use the Paste Special Cell Formatting Method, the Text to Column Method, and the VALUE Function to convert text to numbers in Excel.read moreDate to Text in ExcelNumbers to Text in ExcelNumbers To Text In ExcelYou may need to convert numbers to text in Excel for a variety of reasons. If you use Excel spreadsheets to store long and not-so-long numbers, or if you don’t want the numbers in the cells to be involved in calculating, or if you want to display leading zeros in numbers in cells, you’ll need to convert them to text at some point. It’s useful for displaying numbers in a more readable format or combining numbers with text or symbols. The methods listed below can be used to accomplish this.read moreColumn Function in ExcelColumn Function In ExcelColumn function finds out the column numbers of the target cells in excel. It takes one argument which is the target cell as reference. Note that this function does not give the value of the cell as it returns only the column number of the cell. read more