Excel > Excel 2007 Advanced > Data Management

Converting Text to Columns

The Text to Columns Excel feature allows you to split the contents of a cell into different columns. This is usually performed on a persons first and last name, splitting them into seperate columns.

There are two methods for splitting text into columns:

Delimited: The data is seperated based on the location of a special character such as commas or a space

Fixed Width: You specify a fixed column break location

Before splitting text to columns, ensure there is enough blank columns next to the data being split. Otherwise the data will replace the existing data possibly losing essential information.

Splitting data using a delimiter

Possibly the more popular way of splitting text into columns is using a delimiter such as commas or a space.

  1. Select the cell(s) you want to convert
  2. Click the Data tab on the Ribbon
  3. Click the Text to Columns button in the Data Tools group on the Ribbon
  4. The Convert Text to Columns Wizard dialogue box appears asking which method of converting you would like to use, and showing a preview of the data
  5. Select the Delimited option and click Next

    Convert text to columns step 1

  6. Now you need to select the delimiter being used to seperate the text in your data. In this example it is a space. Check the appropriate box and click Next
  7. The preview changes showing the seperation of the text.

    Convert text to columns step 2

  8. Select a format for each column of data. Select the column in the Data preview area and then select the appropriate format in the Column data format panel
  9. Convert text to columns step 3

  10. Click Finish
  11. The data is split into different columns

Splitting data using a fixed width

You can also decide where to split the data with a fixed column break

  1. Select the cell(s) you want to convert
  2. Click the Data tab on the Ribbon
  3. Click the Text to Columns button in the Data Tools group on the Ribbon
  4. Select the Fixed width option in the Convert Text to Columns Wizard and click Next
  5. Click in the Data preview area where you want to place a break line
  6. A line appears showing where the data will be seperated

    Break fixed width columns

  7. Add additional lines if necessary and click Next
  8. Select a format for each column of data. Select the column in the Data preview area and then select the appropriate format in the Column data format panel
  9. Click Finish
  10. The data is split into different columns

Follow us on

Facebook  Twitter  You Tube