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_text The substring you want to look for (such as a space).
within_text The string in which you want to look (such as the person’s full
name).
start_num The character position at which you want to start looking (the
default is 1).

FIND() returns the character position of the first character of find_text. If you have a person’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])

text The string from which you want to extract the substring.
num_chars The number of characters you want to extract from the right (the default value is 1).

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 circumstances to extract the second word in any two-word string.

Figure 5.8 shows a worksheet that uses both formulas.

• 5 Tips and Tricks for your iPhone [/caption]I’m not really a read the manual kind of guy.  It has nothing to do with being a typical man...
• 6 Tennis Tips and Tricks Tennis tips and tricks are like small tweaks that you can make to your game in order to improve your...
• 10 Essential iPad Tips & Tricks [/caption] Any idiot can use an iPad at a basic level. It's designed that way. But even still, there are...
• 100 Quick Tennis Tips pt 2 Here are 100 quick and easy tennis tips to get your game going. We're going to spread this list out...
• Tips for Serving Here are some more useful tennis tips for improving your serving game. If you want to make improvements in your...