If you have a cell in Excel 2010 that contains a lot of text, you may wish to separate it into several columns. This can only be done if there is a logical character that separates the text, such as a comma, semi-colon or full stop. For example, you may wish to take cells that contain Last Name, First Name and separate it into two different columns.
- Open the worksheet that contains the text you would like to convert to columns.
- Select the cells that you would like to convert.
- On the Data tab, click Text to Columns in the Data Tools group.
- Choose the format of your current data. Select Delimited if the text contains a character such as a comma, tab, space or semi-colon to separate the various fields. Otherwise select Fixed Width if there are a certain number of spaces between each field.
NOTE: in this tutorial, we will be looking at the Delimited option.
- A preview of your selected data appears below. Click Next.
- Select type of character that separates the various fields. You can select as many as are applicable. If you would like to include your own characters that aren’t listed, select the Other checkbox and enter the specific character in the field provided.
NOTE: If you have some fields where the selected delimiter appears several times in a row, you should select the Treat consecutive delimiters as one checkbox. This ensures that you won’t have several blank fields where only the delimiter character was found.
- A preview of the data in columns appears below, according to the delimiter selected. Click Next.
- You now need to choose the format for each of the columns. Select the column heading in the Data preview and then select a data type from the Column data format options.
NOTE: If there are some columns that you don’t want to have included in the text to columns conversion, select the Do not import column (skip) option for the data type of those specific columns.
- Once you have selected the data type for each column, click Finish.
- Your text will now appear in several columns, depending on the number of delimiters in the original list.
That’s it for converting text to columns. Hope you found it useful – don’t forget to take a look at some of the other tutorials about working in Excel 2010.