How To Use Concatenate In Excel

In the context of data, concatenate typically refers to joining of two or more  strings or elements. There are a few different approaches we can take when looking to merge strings of characters together using Excel.  The built in formula for this makes it super easy to simply merge cell after cell, or include additional formatting and characters for readability. In this simple step by step guide you will learn how to easily use the formula for concatenate in Excel.

The Excel CONCAT Formula

The CONCAT function in Excel is designed to concatenate or combine multiple text strings or cell values into one. There is also a similar formula called CONCATENATE, which is a legacy function that has been replaced by the newer CONCAT function in recent Excel versions:

=CONCAT(Text1, Text2)

This is quite an open formula too, we aren’t just limited to two text values and could continue with text 3, 4, 5 and so on if we were working with a lot of values.
Let’s look at a quick example using month and date to display the combination of both

All that needed to be done was selecting cells B3 and C3 and we now have a result that says Feb5. This isn’t the most readable format though, but can easily be resolved.

Combining CONCAT with Strings of Characters

Using quotation marks we can also insert a specific string of characters into our formula. If we simply wanted a space the formula syntax would then look like this:

=CONCAT(Text1, ” “, Text2)

A blank space between the two quotation marks will literally insert a single blank space between the two cells that we select.

We could also use a combination of spaces, dashes, commas and more. Some example outputs using these are below:

Some of these are now far more readable than before, all with a simple addition to the formula.

Contatenate Using the ‘&’ Symbol

In excel you can also concatenate cells using the ampersand symbol (&) rather than a full CONCAT formula.
The general syntax would look like the below:

=Text1&Text2

While the syntax is slightly different the outputs will be identical, which we can see below using the same cell contents as our examples above:

Merging text in excel just comes down to personal preference and which you find easier to use. It can be argued that the CONCAT formula is a fair bit more readable though with the use of parenthesis, spaces and the CONCAT name appearing there which makes it more clear what is happening when doing a quick scan of formulas. They both get the job done.

Outside of dates, this can be a super handy way of merging peoples names and when combined with the PROPER formula can also fit formatting issues as we can see in this guide.

There are some important caveats to note when working with a concatenation. In the above examples we are working with what looks like dates, but even though we have outputs such as Jun-21 these are text outputs and can not be used in any functions related to dates.
On a similar note if we were to concatenate numbers such as two 5’s and get an output of 55 this is also text and not the number 55 so it can not be used in any mathematical formulas.

That being said, you can easily convert this by taking the result of a concat formula, copying and pasting it back in as values only and then changing the data type to number or date. From here you can perform any analysis you like on the contents of the cell.

This covers everything you need to know about concatenating in Excel. For more guidance and tutorial head over to our Excel page here.