A subscriber wrote to me recently with a problem. It seems he had copied information from the Internet and pasted into Excel. The problem was that all of the information was now contained in one column. He wanted to know how he could prevent that from happening in the future. I have received inquires in the past regarding this same area of interest. Sometimes the data in question had been copied from a database or an Word document.
Obviously, the information you have copied is not very useful to you if it is all contained in one column. In order to efficiently use your data you need to have the sorting, filtering and calculation options available to you and for that you need to have your data moved to separate columns.
Luckily, Excel provides you with a very efficient method of accomplishing this regardless of how many rows of data are involved, the Text to Columns function. Having said that, Text to Columns works best when using data from a plain text file. There must be some elements of consistency to your file; each row must be delimited for example, by a line break or a comma, etc. or contain a fixed number of characters for each column.
Follow the steps below to learn how:
- Examine your data to determine how it can best be delimited. Is each field separated by a comma or space?
- Select the cells you would like separated into columns. Bear in mind that this must be a single range of data and must be one column wide only.
- Make certain there are ample empty columns to the right of the selected data to accommodate the separation.
- Now you will open the Text to Columns dialog box:
- Click on the Data tab and in the Data Tools group, click on Text to Columns.
- The Wizard will now open to guide you through the rest of the process.
- Select the type of data you are working with (i.e., delimited or Fixed Width)
- Click Next.
- You will now tell Excel which delimiter is used in your data (if you are using delimited data) and you will see a preview of how your data will be separated. Assuming everything is in order, click Next.
- In the next step in the Wizard, the default format for each column is General and General will convert numeric values to numbers, date values to dates and everything else to text. Should you want, you can set a different format for any column. As you click each option, an explanation of that option will be displayed to the right.
- If a column contains numeric data with leading zeros that you want to keep, you will need to set the format to Text.
- If there is data that you do not want or need, you can select Do not import column (skip).
- Click Finish.
Your data will now have been separated into columns.