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,
Step4. To continue,
verify that the Delimited radio button is selected,
Step5. To deselect the Tab character as the delimiter,
Click the Tab checkbox
Step6. To designate the comma as the delimiting character,
Click the Comma checkbox
Step7. To continue through the wizard,
Step8. To complete the wizard,
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
Step2. To begin the formula, type:
Step3. To make the first name part of the result,
Click cell C4
Step4. To insert a space before the last name, type:
& " " &
Step5. To add the last name to the result,
Click cell B4, press: Enter
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
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.
Step11. Save the file.