A good example is when you have first, last, and middle names in separate columns and you want to join these together into one name. This is referred to as “concatenation.” In this example, we have first, middle, and last names shown separately in a table. I’ll use simple concatenation to join these separate names together into a single name. The easiest way to concatenate is to use the operator for concatenation: the ampersand character. For example, I can join Susan and Brown using the formula =B5&D5. Notice that there is no space separating the names. If you need space or punctuation, you’ll need to add it to your formula as “literal text.” Literal text needs to be enclosed in double quotes, and I need to add a second ampersand to the formula since I’m now joining three items—cell B6, a space, and cell D6—instead of just two. I can take the same approach to include the middle initial of the name. I just need to add two spaces as literal text. However, notice that if I copy this formula down, I get an extra space in the name Tom Smith since there is no middle initial. To adapt the formula to handle an optional middle initial, I need to add some conditional logic. In this case, the IF function will work well. To test for the middle initial, I’m going to use the ISBLANK function. If there is no middle initial, ISBLANK will return TRUE and we’ll use the formula for first and last name only. When there is a middle initial, ISBLANK will return FALSE, so we’ll use the formula that combines all three names. So, to recap, I’m using IF to test for a middle initial. If it’s blank, we’ll combine the first and last name; and if it’s not blank, we’ll combine all three names. In this video, we looked at how to concatenate using the ampersand character. You can also concatenate using the CONCATENATE function. We’ll look at this option in a separate video.
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.
