Split Full Names in a Spreadsheet
Sometimes a computer system will create a spreadsheet with a person’s
full name stored in a single cell. For example, the following table
shows many full names in column A.
| A | B |
|---|
| 1 | Full Name | |
|---|
| 2 | Gonzalez, Enrique | |
|---|
| 3 | Smith, Eugenia | |
|---|
| 4 | McWilliams, Albert | |
|---|
| 5 | Dalton, Wilhemina | |
|---|
If we want the full names separated into two columns, we can use the
Excel LEFT and RIGHT functions to split the
names. To obtain the family name (also known as the surname), we use the
LEFT and FIND functions as shown in the
formula in cell B2. The FIND function finds where the comma
occurs in the full name, and the LEFT function copies all
the characters from the beginning of the name up to but excluding the
comma.
| A | B |
C | D |
|
|---|
| 1 | Full Name |
Family | Given |
| |
|---|
| 2 | Gonzalez, Enrique |
=LEFT(A2, FIND(", ", A2) - 1) |
|---|
| 3 | Smith, Eugenia |
| | | |
|---|
| 4 | McWilliams, Albert |
| | | |
|---|
| 5 | Dalton, Wilhemina |
| | | |
|---|
To obtain the given name, we use the RIGHT and
LEN functions. We use the LEN function to
count how many characters are in the full name after the comma. We use
the RIGHT function to extract that number of characters as
shown in the formula in cell C2 below.
| A | B |
C | D |
|
|---|
| 1 | Full Name |
Family | Given |
| |
|---|
| 2 | Gonzalez, Enrique |
Gonzalez |
=RIGHT(A2, LEN(A2) - LEN(B2) - 2) |
|---|
| 3 | Smith, Eugenia |
| | | |
|---|
| 4 | McWilliams, Albert |
| | | |
|---|
| 5 | Dalton, Wilhemina |
| | | |
|---|
| A | B |
C | D |
|---|
| 1 | Full Name |
Family | Given |
|
|---|
| 2 | Gonzalez, Enrique |
Gonzalez | Enrique | |
|---|
| 3 | Smith, Eugenia |
| | |
|---|
| 4 | McWilliams, Albert |
| | |
|---|
| 5 | Dalton, Wilhemina |
| | |
|---|
After writing the two formulas, we can copy and paste them in all
rows. Notice how using the FIND and LEN
functions enables the LEFT and RIGHT functions
to extract the correct number of characters in every row.
| A | B |
C | D |
|---|
| 1 | Full Name |
Family | Given | |
|---|
| 2 | Gonzalez, Enrique |
Gonzalez | Enrique | |
|---|
| 3 | Smith, Eugenia |
Smith | Eugenia | |
|---|
| 4 | McWilliams, Albert |
McWilliams | Albert | |
|---|
| 5 | Dalton, Wilhemina |
Dalton | Wilhemina | |
|---|
If we leave the formulas in columns B and C, we will also have to
leave the data in column A. In other words, the spreadsheet will contain
each person’s full name and a copy of his family name and a copy of his
given name.
| A | B |
C | D |
|---|
| 1 | Full Name |
Family | Given | |
|---|
| 2 | Gonzalez, Enrique |
Gonzalez |
Enrique | |
|---|
| 3 | Smith, Eugenia |
Smith |
Eugenia | |
|---|
| 4 | McWilliams, Albert |
McWilliams |
Albert | |
|---|
| 5 | Dalton, Wilhemina |
Dalton |
Wilhemina | |
|---|
We can eliminate the repeated names if we copy the names in columns B
and C and then use paste special to paste only the values. Because the
formulas in columns B and C will be replaced with the text values, we
can delete column A.
| A |
B | C |
|---|
| 1 |
Family | Given | |
|---|
| 2 |
Gonzalez | Enrique | |
|---|
| 3 |
Smith | Eugenia | |
|---|
| 4 |
McWilliams | Albert | |
|---|
| 5 |
Dalton | Wilhemina | |
|---|