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.
Table of Contents
Convert Text to Columns in Excel
Let’s understand how to convert text to columns in excel with examples.
As the name itself suggests, it is used to split the text into multiple columns in Excel. For example, if you have a first name and last name in the same cell, you can use this to quickly split these into two different cells.
Excel Text to Columns or Split Cells in Excel is used in data cleaning, data re-arrangement, data manipulation. It can be difficult to work with a lot of text in a single column.
There are so many complex formulas in excel that can help you in splitting your huge text into adjacent columns; it is so complex and confusing.
It’s Excel’s great tool for splitting up text into more manageable columns. Moreover, it is much easier than trying to work with formulas to do the same thing!
This can be very handy when you get your data from databases or you import it from other file formats such as Text or CSV.
In this article, I will explain the useful things that can be done by using TEXT to COLUMNS in excel.
Where to find Text to Columns 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.
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 ‘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 would 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 middle name you need to use the 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.
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 Text to Column excel option (ALT + A + E), this would open up the Text to Column Wizard window.
Step 3: Select 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 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 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 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 9th 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 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 –
- Freeze Columns in Excel
- Text in Excel Formula
- Convert Rows to Columns in Excel
- Excel Split Panes
- Excel Hiding Formula
- Detele Blank Rows in Excel
- Methods to Insert Multiple Rows in Excel
- Insert Hyperlinks Excel
- Convert Text to Number in Excel
- How to Unhide Columns in Excel?
- Date to Text in Excel
- Numbers to Text in Excel
- TEXT Function Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
