CONCATENATE: Excel's Duct Tape

CONCATENATE: Excel's Duct Tape

Back to Tutorial
#### Lesson 23: CONCATENATE: Excel's Duct Tape

### CONCATENATE: Excel's duct tape

#### Combining names

#### Combining numbers and text

/en/excel-tips/how-to-count-cells-with-counta/content/

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.

If you'd like to follow along, you can download our example spreadsheet. Note: If you've never used Excel functions before, you may want to check out our **Functions** lesson from our Excel Formulas tutorial first to learn the basics.

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.

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.

/en/excel-tips/5-tips-for-printing-excel-spreadsheets/content/