Columns of names in a spreadsheet are often entered as full names (first and last). This works great for mailing labels where the complete name is used. It is not convenient when only the first or last name is needed, like when you need to sort on the last name. One method could be to add a couple of columns and one at a time to enter the first and last names—a long and tedious task.
There is a better way and Excel can do it. I am using Excel 2016, but this will work in the previous versions of Excel. This also works in Google Sheets–see below.
Start Excel
Create a new workbook and then enter a list of names.
Text To Columns
Select the names, then Data > Data Tools > Text to Columns.
(In Google Sheets: Select the names, then (Data > Split text to columns > Separator: Space)
The Convert Text to Columns Wizard opens. Click Next.
Uncheck Tab and check Space, and then click Next.
Click Finish.
Change the headings to reflect the new field names.
Concatenate The Names
If the full name is still desired, enter the following formula in C2: =A2 & “ “ & B2
This formula tells Excel to take the value in cell A2, concatenate it with a single space, and then concatenate that with the value in B2.
Note: The + adds two values; the & concatenates them. 1+1=2 and 1&1=11
Press Enter to run the formula in C2.
Then click C2 and locate the fill handle.
Copy the formula down through all the names by double-clicking the fill handle.
We have taken a full name and split it into two and then combined them back into the third column with both names, First Last.
Had we used the formula: B2&”, “&A2 we would have created a cell containing Last, First and C2 would have been Smith, Mary.
A spreadsheet is a valuable tool and learning to use formulas and functions is critical to getting to use its full potential.
Dick
—
