This is the same example we looked at previously: a table which contains first, middle, and last names. In column E, I’ll add a formula that uses the CONCATENATE function to join these names together into a full name. With the CONCATENATE function, just enter each value you want to join together as a separate argument. If I use B5 for the first argument, and D5 for the second argument, we’ll get the name “SusanBrown” without spaces. Just like we saw when concatenating with the ampersand character, we’ll need to add any space or punctuation manually. CONCATENATE joins together things in order, so I’ll need to add a “space” character as the second argument to the function. As usual, all “literal text” needs to be surrounded in double quotes. To extend the formula to handle a middle initial, I need to follow the same process: adding the middle initial in column C, with space characters on either side. As with the SUM function, you can hold down the Control key to add arguments, and Excel will add in the commas for you. You can use this trick to quickly add all the arguments you need. Just add extra arguments where you need spaces, and then come back and convert these extra references to spaces. Just like you saw when we used the ampersand, this will create an extra space in the name “Tom Smith” since there is no middle initial. We’ll use the same approach to fix that problem. By using IF with ISBLANK, we can test for a middle initial. =IF(ISBLANK(C9) If there is no middle initial, we’ll CONCATENATE just the first and last name. If there is a middle initial, that is, ISBLANK returns FALSE, we’ll join all three names. As you can see, the CONCATENATE function and the ampersand are both valid options for joining together values. So, which option should you use? The choice is entirely your personal preference. I generally use the ampersand because the formulas it creates are shorter and, to my eye at least, easier to read. However, the CONCATENATE function also works well and keeps the values you are joining tidy and organized.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
