Splitting and Combining Cell Contents

In each record on this sheet, the entire address sits in one cell. However, there could be advantages to breaking the address components apart. And while the last name and first name are each entered into separate cells, there could be advantages to displaying the full name in a single cell. We will first see how to split the address information apart. Then we will create a field called Full Name to combine first and last names into a single cell.

Separating Cell Contents Using a Wizard

After entering a few addresses, we have decided that for sorting purposes, it might be best to separate these fields. We will see how to break the address components into separate cells.

Step1. To select the cells containing the addresses,

Press & Drag the cell range G4:G11

Step2. Switch to the Data tab, if necessary.

Step3. To begin the Convert Text to Columns Wizard, in the Data Tools group,

Click

You see step 1 of the Convert Text to Columns Wizard:

Excel needs to know what indicators tell how to separate the address components. The commas between the parts make this information delimited. The commas will tell Excel how to separate the street address from the city, etc.

Step4. To continue,

verify that the Delimited radio button is selected,
Click

Step5. To deselect the Tab character as the delimiter,

Click the Tab checkbox

The Tab checkbox is unchecked.

Step6. To designate the comma as the delimiting character,

Click the Comma checkbox

The Data preview window shows that Excel is separating our data
correctly.

Step7. To continue through the wizard,

Click

Step 3 of the wizard gives an opportunity to set the Data Format for each column, but we will leave the default setting of General.

Step8. To complete the wizard,

Click

We see the city, state, and zip code, each in its own column. Now we can easily sort the records in this sheet by city, state, or zip code.

Step9. Expand any columns, if necessary, in order to see all of the data.

NOTE: To avoid replacing existing data, it is important to have enough blank cells immediately to the right of the text you are converting to columns. If extra columns are needed, they can be inserted by going to the Home tab, in the Cells group, and then clicking the Insert button.

Combining Data Using a Formula

Currently the employees' first and last names are entered into separate cells. This is a good idea for sorting purposes. But there may be a need to also display the full name in a single cell.

We will first see how to concatenate, or combine, that information into a new column called Full Name.

Step1. To activate a cell,

Click cell L4

The active cell is now L4, and that will be the location of the first employee's full name.

Step2. To begin the formula, type:

=

Step3. To make the first name part of the result,

Click cell C4

The concatenation operator for text in Excel is the ampersand. We will use it to put the values in cells C4 and B4 together, but we will also add a space between those two values. The space must be in quotation marks.

Step4. To insert a space before the last name, type:

& " " &

Step5. To add the last name to the result,

Click cell B4, press: Enter

The employee's full name appears in cell L4. Let's copy this formula down to the rest of the cells in this region.

Step6. To select the cell containing the formula,

Click cell L4

Step7. To perform the Auto Fill operation,

Point to the fill handle in the lower right corner

You see:

The cursor changes to a thin, solid crosshairs plus symbol. The crosshairs cursor indicates that Auto Fill is activated.

Step8. To use Auto Fill to copy the formula down the column, with the cursor over the fill handle,

Press & Drag the fill handle to cell L24

Step9. Deselect the range.

Step10. Expand the columns so that all of the data displays properly.

As new names are added, the full names in this range will automatically be displayed in column L.

Step11. Save the file.