In this simple step by step guide you will learn how to easily use the Formula for concatenate in Excel.

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.

The Excel CONCAT Formula

The syntax for this one is very straight forward and is as follows:

=CONCAT(Text1, Text2)

That is it. There is also a similar formula called concatenate but this is a legacy function. Let’s look at a quick example using month and date to display the combination of both.

This is quite an open formula too, we aren’t just limited to two text values and could continue with Text3, 4, 5 and so on if we had a lot of values to work with.

As we can see, 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

The general syntax is pretty similar and 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:

With the results being the same this one really just comes down to personal preference and which you find easier to do. 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. Both do the job fine though.

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 pretty much sums up all you need to know in order to understand the basics of how to concatenate in Excel. For more tutorials head over to our Excel page here.