excel-feature

How To Parse Names In Excel

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

 enter-names

Create a new workbook and then enter a list of names.

Text To Columns

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)

choose-file-type

The Convert Text to Columns Wizard opens. Click Next.

convert-text

Uncheck Tab and check Space, and then click Next.

finish-convert

Click Finish.

add-headings

Change the headings to reflect the new field names.

Concatenate The Names

formula

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

run-formula

Press Enter to run the formula in C2.

fill-handle

Then click C2 and locate the fill handle.

formula-copied

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

Scroll to Top

WHY NOT SUBSCRIBE TO OUR NEWSLETTER?

Get great content like this delivered to your inbox!

It's free, convenient, and delivered right to your inbox! We do not spam and we will not share your address. Period!