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


About the Author

Dick Evans

Dick Evans has been in the tech industry for more than fifty years. Beginning his career in the '60s on IBM mainframes, he has been a Programmer, a Data Processing Manager (now called Information Systems Manager), a Consultant, an Assistant Professor, and a Writer. He has been published in a number of computer publications and has spoken at conferences here in the US and abroad. He is currently retired from the faculty at Rhode Island College and consulting/writing part-time, holding teaching seminars, and maintaining a number of Web sites. He maintains a blog with technical “stuff” and has a Web site at rwevans.com.

Leave a Reply

Your email address will not be published. Required fields are marked *