you download your own copy of this workbook so you’ll be able to complete the challenge on your own.
I’m going to jump to that challenge by choosing the worksheet labeled Mayoral Candidates.
The data on this worksheet comes from a web page that you can find the link to, from a particular
Monterey County election. And what we have here, is a set of last names, first names, and middle
names. And, the two different challenges are: we want to be able to create complete names starting
with last name first, formatted a particular way: last name, comma, space, first name, space, middle
name. And we also want to be able to create a column of complete names that start with the first name
first, formatted: first name, space, middle name, space, last name.
So, this challenge requires three different functions. We need the Concatenate() function to concatenate
the elements of the names together to create new names. But we also need an IF() function, because
we’re going to need to do this operation in two different ways: we’re either going to include a middle
name or not, because notice the middle name column. Some of those fields are empty and some of
them aren’t.
The third function we’re going to use, besides IF() and Concatenate() is the Isblank() function. I’m going
to demonstrate to you how that works. If I type “Isblank(” into this cell, and then I choose as my
argument the value in the middle name column,
if that value is empty, not a space, but actually empty, then Isblank() will return a value of TRUE. That’s a
logical value TRUE. Alternatively, if the middle name is not empty,
my Isblank() will return the Logical value FALSE. So, I’m going to build my Concatenate() functions into an
IF() function that uses as its logical test the Isblank() argument to find out if there IS a middle name to
include in the concatenate formula. So, if Isblank() is TRUE, then I’m going to use Concatenate()
without the middle name. So, with the last name going first my formula will be: last name,
comma, to end that argument, followed by the text string: comma space, another comma to end that
argument, and I’ll complete my full name with the first name, and close up my concatenate function.
Comma to end that “value if true” argument for this if() function. Now I’m in the “value if false”
argument. So, if it’s FALSE that the middle name is blank, that means there IS a middle name and I’m
going to have a Concatenate() function that includes the middle name, so my first argument will still be
the last name. I’ll still follow it with comma space.
Then I’ll Follow that by the first name.
And I’ll Follow that by another space,
before I finish with the middle name.
After I closed my second Concatenate function, which is my “value if false” to this IF() function, then I
have to close the IF() function.
And I can copy that same formula down the column by double clicking on the plus sign.
Now my challenge is to do the complete name starting with the first name, and I still need two different
concatenates because I’m going to do it differently if there is a middle name, versus if there is not.
We’re going to use the same logical test: Isblank(),
then Concatenate()
starting with the first name.
If
my middle name is blank, I’m going to have: first name, space, last name,
close up that first Concatenate(). Now, I’m in the “value if false.”
In this case,
my middle name is not blank, so I’m going to follow the space after the first name with the middle name,
follow that with another space,
and finish up with the last name.
Now I’m in the end of the “value if false” argument of my If() and I’m going to close up my if(),
and now copy the same formula down the column.
Thank you very much!