You have copied data from a large table on a webpage and pasted it in Excel, but the data is only shown in one column instead of a table? Or you simply want to separate names at every space character? These (and many more) problems could be solved with the “Text to Columns” function.
Steps to separate text into columns
As the name already says, “Text to Columns” divides text, which is in one cell, into separate columns. We got the above list of car manufacturers and car names in one column. Now we want to separate the car names (Golf, A3) from the manufacturer name (VW, Audi): (the numbers are corresponding with the numbers on the picture)
- Select all cells which you want to separate into different columns.
- Click on “Text to Columns” on the Data ribbon
- Follow the steps on the screen: If you want your text to be separated by a distinct character as space or comma, choose “Delimited”. If your text should be separated after a certain amount of characters (no matter what characters), choose “Fixed width”.
- Click “Next”.
- Choose which character should separate your text. Whenever this character is found within your text, the text will be divided into columns.
- Click “Next”.
- You can set the format of each column. The data is often recognized correctly although sometimes (for example, when you deal with dates) you have to manually change the number format. In our example, we don’t have to change anything.
- Click “Finish”.
Now our data will be separated into two columns. On the last screen (9), you can furthermore determine the function of “.” and “,” as these characters vary in some languages.
