In this case we want to create complete names
from the different elements that we have: the Last Name, the First Name, and the Middle Name. But notice, the Middle Name may or may not be blank (“”). If it is blank (“”), we don’t want to accidentally insert an extra space into the final name result. So we’re going to use an IF() Function to have two different outcomes: 1: creating the name WITHOUT the Middle Name, and 1: creating the name WITH the Middle Name.
For the Last Name first version,
we’ll use the Isblank() formula (Function) to test the Middle Name,
and then we put the Last Name first in our Concatenate() Function,
follow that with a “, ” (comma space),
and follow that with the First Name. Close up the Concatenate(). But if it’s FALSE that that Middle Name is blank (“”), then we’re going to concatenate the three names together,
starting again with the Last Name,
followed by “, ” (comma space), followed by the First Name,
and then another ” ” (space),
finishing with the Middle Name.
And we close the IF() Function with the parenthesis.
And you can see the complete function with all of its elements: the Logical Test argument is the Isblank() Function and the Value_If_True argument is the first Concatenate() Function, the Value_if_False argument is the second Concatenate() Function. Now you might want to try this on your own for the Complete Name with First Name first, before you watch the solution. You could pause this video, and then come back if you have your own companion workbook.
To do the Complete Name with the First Name first, I’m going to again use the IF() Function with an Isblank() to test the contents of the Middle Name. If it is TRUE that it’s “” (blank):
I’ll start with the First Name,
follow that with a ” ” (space), and finish with the Last Name.
If it’s FALSE that the Middle Name is “” (blank),
then I’ll start with the First Name, follow that with the ” ” (space), and then include the Middle Name, followed by a ” ” (space), finishing again with the Last Name.
And I’m going to close up the IF() function, and again I’ll take a look at the entire function. You see all the elements: the First Name, you can’t see in the video, the screen tips, but if you look at your own file, when you double click into the cell to see the entire formula, you’ll also be able to tell which argument you’re in, in the function. When I click on this Concatenate(), the first Concatenate(), my screen tip below the function tells me that’s the Value_If_True argument to the IF() Function, and so on.
Now, I can again double click on the black + (plus) sign in the bottom right corner, and I have the formula propagated down. Thank you very much.