How to Concatenate in Excel:
Combine Strings Easily [2 Simple Methods]

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

The ‘CONCATENATE’ function is one of the most awesome ‘Text Functions’ in Excel.

It’s one of the text-combining functions that would save you a lot of time.

Gone are the days when you had to re-type every single word needed somewhere inside the sheet.

If you like that, then you’ve come to the right place! 🤗 This tutorial would teach you the basics of ‘CONCATENATION’ in Excel and the 2 simple methods of doing it.

Now, consider Bob’s problem:

Bob’s boss shared to him a spreadsheet that contains the information of all his boss’ employees. His boss wanted him to copy the addresses to a Word document.

It looks something like this but with 253 entries:

However, the street and city locations are in different columns. Copying them directly into a Word document creates an unnecessary table (plus, it looks weird).

Typing them one by one isn’t an option (unless Bob takes overtime…until morning).

His only option is to combine the street and city names of an employee into a single cell before copying them to a text file.

This is where the ‘CONCATENATE’ function shines.

If you have a problem similar to Bob, then you’ve come to the right place. This article would teach you how to use Excel’s ‘CONCATENATE’ function to join together the text or value inside different cells into a single cell.

Kasper Langmann, Co-founder of Spreadsheeto

But before we go into details on how to use it, let’s understand first what the ‘CONCATENATE’ function does.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file

What is Excel’s CONCATENATE Function?

Concatenate is a technical word for linking things together. It basically means to combine or to join things together.

This function allows you to combine the contents of different cells into a single cell. This is a simpler and more limited version of the new ‘TEXTJOIN’ function we wrote in the past.

Kasper Langmann, Co-founder of Spreadsheeto

It’s a bit different compared to merging cells. Merging the cells means to physically merge cells into a single cell. This only keeps the upper-left cell’s value and discards the rest.

merge and center only keeps the upper-left value

But the ‘CONCATENATE’ function literally combines the text or content inside different cells into a cell.

And you can do that with 2 different methods:

  1. The CONCAT Method
  2. The Ampersand Method

The CONCAT Method

The syntax for this method is simple:

=CONCAT(text1, text2, [text3],…)

The arguments (text1) represent a text string, cell reference, or a formula-driven value. Each text string must be referenced by a cell value (like “D2”) or a specific string (like “21 Diamond Ave.”).

By the way, old versions of Excel (below 2016) use ‘CONCATENATE’ in the syntax itself instead of ‘CONCAT’. The syntax was =CONCATENATE(text1, text2, [text3],…).

Kasper Langmann, Co-founder of Spreadsheeto

Now, let’s try the ‘CONCAT’ method with Bob’s problem above. Let’s combine the “Street” and “City” address of Andrew Evans into a newly made column called “Location”:

Simple, right?

But if you notice, there’s not a single space between the names. It’s like they were just smashed right next into each other!

They need air. They need space in between.

So what you should do is tell Excel to add a “space” in between the two strings.

To do that, simply add “ “ as a second string:

The result would be:

But the thing is, the address still doesn’t look appropriate. You’d like to add a comma in between the street name and city name. Maybe even add the word “City” on the last part of each entry.

To do so, simply add the characters and words you like to include between a pair of quotation marks (and don’t forget about the space):

adding comma and the word city on the concat syntax

With that, you’ll get:

Easy! But you’ll be amazed by how the second method makes it even simpler!

The Ampersand Method

The ‘ampersand’ (&) operator is one of Excel’s operators. It serves as a text concatenation operator.

The syntax of the ‘&’ operator is simple:

=text1 & text2 & text3 &.. textN

No kidding. That’s literally it.

If we try it with Mark Knight’s address from Bob’s problem above, the syntax would be:

joing street and city address of mark knight using ampersand operator

Giving you:

As you see, it displays the same results with the ‘CONCAT’ method. You would also need to add the same elements as with the ‘CONCAT’ method to achieve the desired result.

This would mean adding a comma, space, and “City”:

Adding comma and the word city on ampersand syntax

You’ll then get the ideal result:

address now has comma and the word city on the last part

Like what I said earlier, this method is even simpler than the first.

If you’re interested in knowing the difference between the two, it’s just the 255 strings limit of the ‘CONCAT’ method. There are no limitations when using the ‘ampersand’ operator

Kasper Langmann, Co-founder of Spreadsheeto

However, it won’t matter much which method you’ll use since 255 is a big number. In real-life situations, there’s not a single task that would require you to combine that many strings.

CONCATENATE Columns or Rows

Let’s go back to Bob’s problem. 

Although Bob knows now about the ‘CONCATENATE’ function in Excel, it doesn’t mean his problem will go away. He has to know how to apply the formula to the succeeding cells below the column.

There are two ways to do this:

  1. Copy-paste the cell with the syntax to all the cells in the entire column
  2. Drag the fill handle down to copy the formula to other cells

To copy-paste, just select the cell with the syntax and hold CTR + C. To paste the formula to cells, select the cells then hold down CTR + V.

how copy pasting syntax works

To use the fill handle, just click the cell with the syntax and drag the handle on the bottom right side of that cell down.

how dragging down the fill handle works with concatenate function

That’s it! Simple, right?

Excel’s ‘CONCATENATE’ function has lots of real-life applications. Knowing how to do it would help you save some time from typing the same texts over again.

Kasper Langmann, Co-founder of Spreadsheeto

Now, try doing it on your own. Keep practicing until you can do it with your eyes half-closed. 😊