Using Concatenate in Excel

The Microsoft Excel concatenate function lets you join two or more strings together, and it is one of many native functions and excel formulas of Excel which is categorised as a string and text function. As such, it can be used on the worksheet as a worksheet function or entered as part of a formula within a cell in the worksheet.

If you are finding yourself pulling your hair out trying to manage a large Excel spreadsheet full of data and you want to create sentences which can be filled automatically with this data and information, then the concatenate function is exactly what you need to use. It is used to join together the values of cells within your worksheet.

Concatenate is not available in newer versions of Microsoft Excel (2016 and beyond) as it was replaced by the concat function. It’s best to follow this guide by opening a Microsoft Excel worksheet and doing it bit by bit as we go along.

Using Concatenate in Excel

Excel concatenate is very easy to use, and the syntax for the function is as follows:

CONCATENATE( text1, [ text2, . . . text_n ] )

The parameters within this string are the pieces of information which you want to join together. With concatenate, you can join up to 255 different strings together with an absolute maximum of 8,192 characters.

There are a lot of different ways you can use concatenate to perform different actions.

1. Using concatenate in Excel to join two cells
This is the basic concatenate function – joining two strings together – and, as above, you can join up to 255 different strings together with one concatenate command. Entering the formula for this is very simple:

The result in C1 and C2 would then be goodnight and goodbye respectfully.

2. Using concatenate to insert spaces and punctuation between joined text
If you want to join two cells but have a space in between them, you can add a space into the concatenate formula very easily. This is useful when you are processing information such as first and last names or street names.

The result in C1 and C2 would then be John Smith and Joe Smith respectfully.

In addition to adding spaces with quotation marks, you can add in pieces of punctuation. It’s important to use proper spacing within the quotation marks to ensure that the end result would read properly. One use for this is date ranges.

The result in C1 would be 01/01/2018 – 31/01/2018 whereas the result in C2 would be Monday-Friday because no spaces are being used.

3. Using an ampersand (&) in lieu of concatenate
The “&” sign – ampersand – performs the exact same function as writing concatenate does. This can be used to create shorter formulas, however, its use is negligible in longer formulas as it can very quickly cause clutter and result in something which is difficult to read because you must use the ampersand between each value which you are wanting to concatenate.

After this, in F8, create the following formula which will tabulate the principal paid – “=$D8+$F7” and then do it again for cumulative interest in G8 – “=E8+G7”.

This would perform the exact same function as above, resulting in John Smith and Joe Smith being the result in C1 and C2 respectively.

Limitations of the Concatenate Function in Excel

If you have a list of text values in one column and would like to use the concatenate function to put them all into one single string, using the concatenate function would take up the arguments individually. This is fine, but if your list of values and arguments is long then this can take a lot of time.

This is one limitation which the concatenate function has – it is not possible to easily pass a range of values as one argument – because if you pass a range value, it picks up the values of other cells within the same row where concatenate is typed out.

Despite this, it’s still a very useful function which is great if you have lots of mismatched information you need to join together.

It is important to note that the $ sign in the formulas represents relative reference and not U.S.