Splitting cell contents

Available Files

The following files are available for download:

Download Exercise Files

All four parts of the employee addresses (street address, city, state, and zip code) have been entered into a single column. While this may work for some purposes, it isn't ideal. With the data elements in one cell, it cannot be sorted, filtered, or otherwise analyzed by any of the individual elements. For example, you could not use this data to see how many employees live in Indiana or to create mailing labels sorted by zip code.

Excel has an easy way to separate data into multiple columns. However, before beginning this process, you will want to ensure that there is no data in the cells to the right of the column to be split. Any data in those cells will be overwritten by the split data. The addresses are in column H and the columns to the right are all empty.

To make cell H2 the active cell,
Click in cell H2
To select the data in column H, on the keyboard, press:
Control key + Shift key + Down Arrow key

With the data to be separated selected, we can now open the Convert Text to Columns Wizard. The button for the wizard is on the Data tab of the Ribbon.

To move to the Data tab, on the Ribbon,
Click the Data tab
To open the Convert Text to Columns Wizard, on the Data tab,
Click

The Convert Text to Columns Wizard opens:

Convert text to columns wizard. The contents are described below.

The wizard begins by asking what type of data is selected, delimited or fixed width. The addresses are comma delimited. This means each individual part of the address is separated by a comma.

Below the delimited or fixed width options is a preview of the selected data. Currently, this shows the addresses in a single cell.

Let's move on to the next step of the wizard.

Confirm that the Delimited radio button is selected.
To move to the next step in the Wizard,
Click

The Convert Text to Columns Wizard moves to Step 2 of 3. In this step, we can tell Excel which delimiter is being used. By default, the Tab delimiter is selected. Let's change this to the Comma delimiter.

To remove the Tab delimiter, in the Convert Text to Columns Wizard,
Click the Tab checkbox
To select the Comma delimiter, in the Convert Text to Columns Wizard,
Click the Comma checkbox

The preview area now shows the four parts of the address separated into different columns.

To move to the next step of the wizard,
Click

Step three of the wizard gives us the option to select each column and choose a format for that data. For now, let's leave these all as General.

To accept these changes and close the wizard,
Click

The four parts of the addresses are now in separate columns. The columns of newly separated data (City, State, and Zip Code) do not have column headings. Let's add those now.

To add the column heading to the City data,
Click in cell I1, type: City Tab key
To add the remaining column headings, type:
State Tab key Zip Code Enter key

The new columns are all a bit too wide for the data they contain. We can resize all of the columns to their ideal width by selecting all of the columns and Double-Clicking between two of them.

To select the four new columns, in the column header,
Press & Drag from H to K
To resize the columns,
Double-Click between any of the selected column headers

To save the workbook, on the keyboard, press:
Control key + S

The columns now fit the data they contain.

Splitting cell contents

Available Files

Step 1. Select the contents of column H.

Step 2. Open the Convert Text to Columns Wizard.

Step 3. Confirm that the data is delimited and move to the next step.

Step 4. Indicate that the content is comma delimited.

Step 5. Leave the data format as General and close the wizard.

Step 6. Add the headings to the city, state, and zip code columns.

Step 7. Select and resize the columns H through K.

Step 8. Save the workbook.