For All Your Training Needs                                                                      [ Home ] [ Up ] [ Site Map ] [ Search ]

Microsoft Excel
Split Column Contents into Multiple Columns

Suppose that you are working in Microsoft Excel with a table listing your business contacts.  The table contains one column with last names and first names.  You would like to split the names so that the last name and the first names are in separate columns.  Follow the steps listed below to split a column's contents into two or more columns.

Insert Blank Columns

• You will need to insert blank columns to the right of the column you wish to split

  • This will create a space for the split data to appear

• Select the column to the right of the one you want to split by placing your mouse pointer at the top of the column

  • When the mouse pointer changes to a black down-pointing arrow, click to select the column

• Menu Path

  • Insert menu

  • Columns command

    • Microsoft Excel will insert the new column to the left of the selected column

  
  Mouse Pointer at Top of Column

Select the Column to be Split

• Place your mouse pointer at the top of the column you wish to split

• When the mouse pointer changes to a black down-pointing arrow, click to select the column

Menu Path

• Data menu

• Text to Columns command

  • The Convert Text to Columns Wizard will display

Convert Text to Columns Wizard - Step One

• Choose the type of data within the selected column

  • Delimited = data that is separated by characters such as commas and spaces

    • In this example, we will choose Delimited

  • Fixed Width = data that has a set length separated by spaces

• Click the Next button to move to Step Two

Convert Text to Columns Wizard - Step One

Convert Text to Columns Wizard - Step Two

• Depending on your answer in Step One, this screen will either prompt you for information about the delimited characters or about the width of each field

• Delimiters = those characters that are used to separate your data

  • In this example we will check both the comma and the space delimiters

• Treat Consecutive Delimiters as One = turn on

• The preview on the bottom of the wizard shows how the data will be split

• Click the Next button

Convert Text to Columns Wizard - Step Two

Convert Text to Columns Wizard - Step Three

• Indicate the type of data in each column shown in the wizard's data preview

• Column Data Format = General, Text or Date

  • If not sure, select General

• Click the Finish button

Convert Text to Columns Wizard - Step Three

Microsoft Excel Information Box

• A window will appear asking if you wish to replace the contents of the destination cells

• Click OK to respond

Contact Names Split into Two Columns

Send mail to Complete Training Management with questions or comments about this web site.
Copyright © 2002 Complete Training Management
Last modified: December 03, 2008