Microsoft Office Tutorials and References
In Depth Information
Comparing, Finding, and Measuring Text
Back in Figure 16-3, I showed you how to concatenate first and last names
together. What if you have full names to separate into first names and last
names? SEARCH to the rescue! (Does that make this a search-and-rescue
mission?) Figure 16-11 shows how the SEARCH, LEFT, RIGHT, and ISERROR
functions work together to turn names into individual first and last names.
Isolating the first name from a full name is straightforward. You just use
LEFT to get characters up to the first space. The position of the first space is
returned from the SEARCH function. Here is how this looks:
Getting the last names is just as simple — not! When the full name has only
first and last names (no middle name or initials), you need SEARCH, RIGHT,
and LEN, like this:
However, this does not work for middle names or initials. What about Franklin
D. Roosevelt? If you rely on the last name being after the first space, then the
last name becomes D. Roosevelt. An honest mistake, but you can do better.
What you need is a way to test for the second space and then return
everything to the right of that space. There are likely a number of ways to do this.
Here is what you see in Column C, in Figure 16-11: