Text to Column in Excel | Split Text into different column

How to use Text to Column in Excel?

Text to Column is a feature in Excel that separate the content of one cell into multiple columns. Selecting a delimiter will help you to separate the content or use the text to column feature more effectively. Text to column feature in Excel deserves a lot of credits. Text to Column is one of the technique in Excel that helps in cleaning our data.

Where do you find Text to Column feature in Excel?

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

2.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 different column?

A long time ago I used to carry out this task by copying the last name and pasting it in different column and same for the first name. And I actually did this work manually copying and pasting in respectively 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:

1. Select the cells that contains the data you want to separate

2. 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 different column?

Answer: If your answer is yes, then you need to select the Delimited Option. If you are thinking about what is fixed width option, I will explain you in the next tutorial of Text to Column.

Click on Next. A lot of option are 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 lot of setting seen in the third steps of the convert text to column wizard. Let’s see what those settings are. All the setting 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 the 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 destination cell is not changed? If I didn’t change my destination cell and clicked on finish then 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 difference. 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 Drop – down > 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.

NOTE: Use the Paste Special option while pasting the data by going into the Paste Special dialog box and Click on Values or directly using the paste special value button.

This was a short description on Text to Column. A 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 option. 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.

Hope you like our tutorial for more cool and amazing trick of excel Like our Excel Superstar Facebook Page and subscribe to our Excel Superstar YouTube channel. Excel Superstar is the leading online training company, which provides Online Excel Course in Hindi. Connect with us and become an Excel Superstar.