Concatenate in Excel – Explained

Often the data in your spreadsheets is not according to your needs, so you may need to replace some strings, split some text or concatenate strings to format the data as per your needs.

String concatenation is the process of joining two or more strings together. Excel gives you the capability of concatenating a series of strings.

Mainly there are two ways to merge strings in Excel and in this tutorial I will be explaining both these methods with suitable examples:

Method 1: Join Strings Using the Excel Concatenate Function

Excel has a predefined formula ‘Concatenate’, the job of this function is to join multiple strings that are passed to it as arguments.

The joined items can be text, numbers, cell references, or a combination of these.

Syntax of Concatenate

The syntax of this function is as follows:

=Concatenate(text_1, text_2, Text_N)

Here ‘text_1’, ‘text_2’, and ‘Text_N’ are strings that are to be combined.

Some Important points about Concatenate Function

    • A single Concatenate formula can be used for concatenating 255 strings.
    • Concatenate formula requires at-least 1 string as an argument, to work.
    • It results in a #VALUE! error if any one of the arguments is invalid. For example, the following formula results in an error.

Examples of Excel Concatenate Function

1. In the below example we have a list of First and Last names.

Concatenate Function in excel

Now if you wish to join the First name and Last name strings, then you can use this function as:


Here, ‘A2’ is the person’s First name and ‘B2’ represents the Last name. While joining these two strings they should be separated by a space “ ” and that’s why in the formula we have used a blank space in between ‘A2’ and ‘B2’.

Note: Notice that the Concatenate function doesn’t leave space in between two words itself.

2. In the second example we will try to join the First Name and the Last Name along with the person’s Title.


Here, we have used a Concatenate Formula as:

=CONCATENATE("Mr. ",A3," ",B3)

In this formula first of all we have used a hardcoded title “Mr. ” and then we are concatenating the First name and the Last name in the same way as we have done in the first example.

Method 2: Concatenating Multiple Strings in Excel using the “&” operator

Another way to merge strings in excel is by using the “&” operator. Actually, this method can come in very handy when you have to join only a few strings. But if you are planning to use this method for concatenating several strings, then it can be quite cumbersome as it doesn’t give you the ease of the actual Concatenate Function.

Syntax of combining strings using “&” operator


Here, ‘Text_1’, ‘Text_2’, and ‘Text_N’ are text strings that are to be joined.

Example of Concatenating string with “&” operator

1. In the below example we will also try to merge a person’s first name and the Last name using the “&” operator.

Concatenate in Excel

Here, we will use a formula:

=A4&" "&B4

‘A4’ cell contains the person’s First name

And ‘B4’ cell contains the person’s Last name

This formula is similar to:


So, What’s the Difference Between Concatenate function and “&” Operator

As such, there is no difference between the results of the Concatenate Formula and the “&” operator. But one thing that differs is that Concatenate Function can only join up-to 255 strings however “&” operator doesn’t have any such limitations.

I personally think that 255 is a huge number and no one practically needs to merge 255 strings. So, the difference boils down to the ease of use. You should use the option with which you feel more comfortable.

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.


  1. I just received a spreadsheet from a friend of mine, and was going through it and found this formula!
    He lives in another country, so I just cant hop in my car and ask him to explain, I would like to work it out for myself, but am totally lost.
    Can you shed a bit of light on this??