Ms Office Tune Up

Your worksheet might contain a column with people’s full names. If you’re preparing the data for export to a database or for a mail merge, you might want to extract the first and last name into separate columns. Similarly, if you want to sort the data on last name, you’d need to extract the last name into its own column.

The method for extracting the first names or last names requires two steps. The first step is to locate the space that separates the first and last names. You can do that using the FIND() function:

FIND(find_text, within_text [,start_num])

FIND() returns the character position of the first character of find_text. If you have a per­son’s full name in A2, then the following expression returns the character position of the space separating the first and last name:

FIND(” “, A2)

The next step is to then use either the LEFT() function to extract the first name or the RIGHT() function to extract the last name. Here is the syntax for the LEFT() and RIGHT()

functions, which return a specified number of characters starting from the left or right of a string:

LEFT(text [,num_chars]) RIGHT(text [,num_chars])

For the first name, use the following formula (assuming that the full name is in cell A2):

=LEFT(A2, FIND(” “, A2) – 1)

Notice how the formula subtracts 1 from the FIND(” “, A2) result to avoid including the space in the extracted substring. You can use this formula in more general circumstances to extract the first word of any multiword string.

For the last name, you need to build a similar formula using the RIGHT() function:

=RIGHT(A2, LEN(A2) – FIND(” “, A2))

To extract the correct number of letters, the formula takes the length of the original string and subtracts the position of the space. You can use this formula in more general circum­stances to extract the second word in any two-word string.

Figure 1 shows a worksheet that uses both formulas.

Figure 1. Use the LEFT()and FIND()function to extract the first name; use the RIGHT()and FIND()functions to extract the last name.

CAUTION

These formulas cause an error in any string that contains only a single word.To allow for this, use the IFERROR()function:
=IFERROR(LEFT(A2, FIND(” “, A2) – 1), A2)
If the cell doesn’t contain a space, the FIND()function returns an error, so IFERROR()returns just the cell text, instead

TIP

How do you extract the last name if the person has three names? You need to run a FIND()func­tion where the start_numvalue comes after the first space. FIND()will then locate the nextspace,which is the one that separates the middle name and last name.To do this,set the start_numargument to the location of the first space, plus 1:
FIND(” “, A2) + 1
You then plug this into another FIND()function: =FIND(” “, A2, FIND(” “, A2) + 1)
Finally, you can then apply this result within the RIGHT()function to extract the last name: =RIGHT(A2, LEN(A2) – FIND(” “, A2, FIND(” “, A2) + 1))

Technorati Tags: Extracting a Person’s First Name or Last Name, Excel, Excel 2007

Popularity: 6% [?]