So we’ve got a definition here of concatenate. Concatenate means to link together. Consider a situation where you have a last name, first name, and a middle name, and you want to link them together to create a complete name. You might want to put them in the format with the last name first, or you might want to put them together with the first name first. In this case with the last name first, it’s common to format it to have a comma after the last name, followed by a space, and then the first name. And if there is a middle name, an additional space before the middle name.
I have two examples here using the CONCATENATE function, and we have comments in the cells that give you some extra information. In the first example we’re concatenating the last name, first name, and middle name together to create a complete name with the last name first. Using the concatenate function we put in text arguments that we want to link together. So see our first argument here is the last name, followed by a second argument that is a comma inside of quotes followed by a space inside the same quotes, and then the third argument to the CONCATENATE function is the first name in cell C8, and that’s followed by a single space within quotes. Anything within quotes is a text string to Excel. And then the middle name is the 5th argument to this CONCATENATE function. So we’ve got five text strings in this CONCATENATE function: the last name, the common space text string, the first name, the space, and then the middle name. In this complete name we also have five arguments but this time we’re formatting it with first name, followed by a space, followed by the middle name, followed by another space, followed by the last name, and you can see the different results here.
Now for your practice, and you might want to try this on your own first, we’ve got the same mayoral candidates that were used in the separating text module, and your challenge to create complete names with the last name first or the first name first. But, there’s an extra challenge here. What if there is no middle name. We don’t want to end up putting extra spaces in the complete name, so we’re going to test to make sure that the middle name isn’t empty. There’s a function called ISBLANK that we can apply to test to see if it’s empty. I’m i’m going to demonstrate this so you can see it in action.
I say =isblank and i’m going to click into this cell that should be blank because there is no middle name for this person, and notice that it’s true that it’s blank. That means that it’s an empty cell. Notice if i put that same test down here for this person’s name, it’s false that it’s blank, because there is a middle initial there. So i’m going to build an if function here that tests for whether the middle name is blank. If it’s true that it’s blank, then i’m going to use my CONCATENATE function to only put together the last name, followed by comma space, followed by the first name. Otherwise I’ll CONCATENATE the middle name into the mixture. I’ll have the last name, followed by the comma space, followed by the first name, followed by a space, followed by the middle name, and then I end my if function.
I’m going to do the same logic if i’m putting the first name first. I’m going to test for the middle name being blank. If that’s true, then my CONCATENATE will only use the first name, space, last name. Otherwise it’ll use the first name, space middle name, space, last name.
So try this on your own and then we’d love to hear from you if you have questions, comments, suggestions, requests… bring them on! Thank you very much!