Text to Column is a feature in Excel that separates the content of one cell into multiple columns. Selecting a delimiter will help you to separate the content or use the text to column features more effectively.
Text to column feature in Excel deserves a lot of credits. Text to Column is one of the techniques in Excel that helps in cleaning our data.
Where do you find Text to Column feature in Excel?
- Text to Column feature is found in the Data Tab > Data Tools Group > Text to Column. This will open the Convert Text to Column Wizard. After carrying out the steps you will get your desired result.
- Another way to Access Text to Column is by using the Excel Shortcut key ALT + A + E.
Example of Text To column in Excel
Taking a very basic example for explaining how can you use text to column to separate the first name and last name in a different column?
A long time ago I used to carry out this task by copying the last name and pasting it in a different column and the same for the first name. And I actually did this work manually copying and pasting in respective columns.
You can also do it. But stop is it possible to carry out this task within a few seconds? Come let’s see on how to use the Text to Column in Excel:
- Select the cells that contain the data you want to separate
- Go to the Data Tab > Data Tool Group > Click on Text to Column
“Convert Text to Column Wizard” Opens up. This Wizard will ask you with 3 basic questions.
a. Does your data contain any character which helps you to separate the data into the different column?
Answer: If your answer is yes, then you need to select the Delimited option. If you are thinking about what is a fixed width option, I will explain to you in the next tutorial of Text to Column.
Click on Next. A lot of option is given.
b. Which delimiters does your data contain?
Answer: There are various delimiter options like Tab, Semi-colon, Comma, Space, or any other character. If your data contains a space that separates your data then select Space. In our example, will Tick on Comma and Untick on Tab.
Click on Next. There are a lot of setting seen in the third steps of the converted text to column wizard. Let’s see what those settings are. All the settings will be explained to you in different tutorials of text to column.
c. Do you want to see the same output as you can see in the Preview Window?
Answer: In the example, we see a Preview window in Step 3 of the Convert Text to Column Wizard. And it questions whether we want to see the same output that is seen in two different columns. Well, yes but I don’t want to lose my original data. For that, I will need to change the destination.
Change the destination from $A$4 to $B$4. What would happen the destination cell is not changed? If I didn’t change my destination cell and clicked on finish then the original data would be replaced. So to not replace the old data with the new data we should change the destination cell.
d. Deciding the format in which you want to display your output?
Answer: Well we can change the format of our data. For example, we change both the format to Text in the custom data format option. In our example, if we would not have changed there won’t be any different. Because by default, all the names are in the text format only.
Click on Finish. And let’s see how it looks.
What I see is there is
Change in the format of our data. Not much a problem, this change can be rectified by going to the Selecting the data > Click on Home Tab > Editing Group > Click on Clear Dropdown > Click on Clear Formats button. (An icon with Pink eraser)
Extra space can be seen which is not needed. This problem too can be solved by using the TRIM function. TRIM formula removes all the extra space to the left and right of the word. Press Enter and copy and paste that data.
This was a short description of Text to Column. Basic information on how can you separate your data into multiple columns. Now we have separated our data. In order to combine our data again then we have many options. But on way is to use the CONCATENATE Function which is explained in the other tutorial.
Practice this example and make yourself ready to learn more about Text to Column.