CONCATENATE: Excel's Duct Tape | How to become an Expert of MS Excel - Class 6 PDF Download

Introduction

MacGyver used it. The Apollo 13 crew used it. Whenever people are in a bind and need to stick two things together, they reach for the duct tape. But what you may not know is that Excel has a built-in function that does pretty much the same thing: CONCATENATE.

CONCATENATE lets you combine two or more things in one cell—and despite the long name, it's actually easy to use. It works the same way in all versions of Excel, as well as in other spreadsheet applications like Google Sheets.

Combining Names

Let's say we have a spreadsheet of contact information with last names and first names in separate columns, and we'd like to combine them to get each person's full name. In the image below, you can see that the first names are in column B and the last names are in column A. Our formula will go in cell E2.

Before we start typing the formula, there's one important thing you need to know: CONCATENATE will combine exactly what you tell it to combine, and nothing more. If you want punctuation, spaces, or any other details to appear in the cell, you'll need to tell CONCATENATE to include it. In this case, we want the names to have a space in between them (so it doesn't say JosephineCarter), so we'll need to add an argument that contains a space. This means we'll need three arguments:

• B2 (first name)
• " " (a space in quotation marks)
• A2 (last name)

Now that we have our arguments, we can type the following formula into cell E2:
=CONCATENATE(B2, " ", A2)

In the current version of Excel, you can use the new CONCAT function instead of CONCATENATE. The two functions work the same way.

Just like any function, the syntax is important. Make sure to start with an equals sign, and separate each argument with a comma. Note: Depending on where you live, you may need to separate the arguments with a semicolon (;) instead of a comma.

That's it! When you press Enter, it should display the full name: Josephine Carter.

• Now you can click and drag the fill handle down through cell E11, and it should display the full name for each person.
  
• If you'd like an extra challenge, try using CONCATENATE to combine the city and state in column F so it looks like the image below.
  

Combining numbers and text

You can even use CONCATENATE to combine numbers and text. For example, let's say we're using Excel to keep track of a store's inventory. We currently have 25 apples in stock, but 25 and apples are in separate cells. We want to combine them into one cell so that it looks like this:

To do this, we'll need to combine three things:

• F17 (number in stock)
• " " (space)
• F16 (product name)

Type the following formula into cell E19:
=CONCATENATE(F17, " ", F16)

Let's say we want it to say We have 25 apples. We'll just need to add an argument at the beginning that says We have:
=CONCATENATE("We have ", F17, " ", F16)

If you wanted to, you could add even more arguments to create more complex statements. Just keep in mind that the syntax always needs to be exactly right, or the formula may not work—and it's easier to make a mistake with a longer formula.