And you can also learn from this workbook. I’m going to go ahead and click onto the Mayoral Candidates worksheet and describe the challenge. We’ve got a column of complete names in column A of this table, and our goal is to be able to extract the last name, the first name, and the middle name or initial, from each name. Not all of the complete names have a middle name or initial, so the challenge, part of the challenge, is to have formulas that will account for the fact that the name may or may not have a middle name or initial to extract.
We’re going to start by finding the length of each complete name
with the Len() Function.
And we’ll find the location of the first space in each complete name, using the Search() Function.
We’re looking for a space. That’s our Find Text. We’re looking within the complete name cell, and our first space, we’re gonna default to starting in location one of the text string.
Now we’re going to look for a second space with the Search() Function. You still use the space for our Find Text. We’re still looking within the complete name. But this time we’re going to start our search at the location of the first space plus one.
And you can see that we have a value error wherever we do not have a middle name or initial.
Now we want to extract the last name. And we look at the formatting of these. We know that every name is
presumed to be formatted as “last name, comma, space, first name, and then possibly: an additional space, preceding a middle name or initial. We know the location of the first space. And we know that it comes after the comma that comes right after the last name. So, we can calculate the length of our last name by subtracting 2 from the value of the first space. Because we know it’s 2 positions further on than the last character in the last name.
So, since we know how long the last name is, we can use the Left() Function
on the text of the complete name, and extract
the number of characters calculated by the location of the first space minus 2.
So, we only needed one
potential value for the last name extraction. Whatever, we just pull the characters from the left of the complete name. But for the first name extraction, it’s going to depend on whether there’s a middle name or not, how we do this. Because, if there is a middle name, we’ll use the location of the second space to help extract the first name. If there isn’t a middle name, we could just use the Right() Function to extract the first name.
Or, we could use the Mid() Function with a different calculation.
So, I’m going to choose to use the Mid() Function to extract the first name, whether it has a middle name or not in the complete name,
but I’m going to use an IF() Function,
so I can choose between which of those Mid() Functions to use. The first thing I am going to do is test to see if I have an error
in the search for the second space.
And if I do have an error, if it’s TRUE that I have an error, and I know I do not have a middle name, and I’ll use my Mid() Function on the complete name text string,
and I’ll start my extraction of my first name at the location of the first space plus one
and the number of characters that I want, if I have no middle name, is the name length minus location of the first space.
Otherwise, if there IS a middle name,
then I’m going to use the Mid() Function on the complete name.
Start getting the first name from again the first space plus one,
but this time the number of characters will be
the second space minus the first space minus 1.
And now let’s see that that looks correct.
Now to get the middle name or the a middle initial: again we’ve got two different possibilities: that there is or isn’t one. I’m going to test for an error in the second space search.
If there is an error, then we know we do not have a middle name and we’ll just have an empty value for our result of this If() Function. Otherwise, we can use the Right() Function, and extract characters from the right of the complete name.
And the number of characters that we want to extract
is the length of the complete name minus the location of the second space.
And then we need to close up our If() Function.
And we have our finished results. Thank you very much.