Excel > Excel 2003 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 menu and select Text to Columns



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

Select the delimiters option and click Next



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

The preview changes showing the seperation of the text.



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



6. Click Finish

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 menu and select Text to Columns
3. Select the Fixed width option in the Convert Text to Columns Wizard and click Next
4. Click in the Data preview area where you want to place a break line

A line appears showing where the data will be seperated



5. Add additional lines if necessary and click Next
6. 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
7. Click Finish

The data is split into different columns

Using Excel Lists Joining cell data

Guides