How to Concatenate in Excel

---

The CONCATENATE Excel function is used to join several text strings into one string. It is often the solution when text which we would like to see appearing together is located in different places, or across several cells. 

Starting with Excel 2016, the CONCATENATE function is being phased out in favor of the CONCAT Excel and TEXTJOIN Excel functions. For the time being, CONCATENATE is still available for backward compatibility.

We will explore how all three functions work, so that you will be able to decide when it’s appropriate to use each one.

---

How to concatenate in Excel

• CONCATENATE function
• CONCAT function
• TEXTJOIN function
• Concatenation operator (&)

---

CONCATENATE

The syntax of the CONCATENATE function is:

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

Each argument may be a cell reference or a text string typed directly into the formula. Only one argument is required, but if you’re using this function, it’s likely that you have at least two. CONCATENATE can accept up to 255 arguments, with a maximum of 8,192 characters.

How to use CONCATENATE

To learn how to use this function, let’s take a look at an example. In the worksheet below, the address elements are all split across five columns.

We can use CONCATENATE to join all five elements into one string. It is important to remember that if a space or some other character (e.g., comma) is required between elements, it has to be manually typed between each cell reference within double quotation marks.

=CONCATENATE(A2," ",B2,", ",C2,", ",D2," ",E2)

Note that since both a comma and space were required after B2 and C2, the text string “, ” was typed as an argument after both cell references.

Concatenate with numeric values

When submitting numeric values directly into a CONCATENATE formula, there is no need to enter the numeric values within quotation marks.

In the following example, the street number will be entered directly into the CONCATENATE formula and will be joined to the cell references to create the full address.

=CONCATENATE(123," ",A2,", ",B2,", ",C2," ",D2)

When numeric values are entered as a string, they are interpreted as numbers. Therefore, typing a space within that argument will have no effect on the value which is returned. If any other delimiter is entered, Excel will return an error message. 

In our example above, we entered a space between two double quotes as our second argument, and this was reflected in the output cell.

If a numeric value is entered as a string within double quotes, then it is converted to text and a delimiter may be placed within that argument since it behaves like any other text value.

Concatenate strings with line breaks

We may want to display some of these elements in separate lines. To do so, we can use the CHAR(10) unicode character to insert a line break.

=CONCATENATE(A2," ",B2,CHAR(10),C2,", ",D2,CHAR(10),E2)

Note that in the above instance, we removed the space and comma after B2 since it was no longer needed with the insertion of the line break character - CHAR(10).

Column F was also formatted to Wrap Text so as to display the results.

Limitations of CONCATENATE

1. CONCATENATE can get a bit cumbersome, especially when adding delimiters. A somewhat common unexpected is that a quotation mark appears in the result string because of omitting a comma between arguments. For instance: 

=CONCATENATE("Good ""morning")

will return a result of Good "morning because no comma was entered to separate the two strings.

2. As useful as the CONCATENATE Excel function is, if the strings to be joined are in a contiguous range, and require no delimiters like commas or spaces, it is unable to accept a range as an argument. 

The following example has elements of a group of phone numbers broken out across four columns.

If we attempt to apply the CONCATENATE Excel function using the range A2:D2 as an argument, Excel will return a #VALUE! error.

This is too bad, but this is where the CONCAT function steps up.

CONCAT

Starting with the 2016 version of Excel, the CONCAT Excel function has joined the family of text functions, and like CONCATENATE, CONCAT exists to join multiple text strings into one string. 

The syntax is:

=CONCAT(text1, [text2]...)

Each argument may be a cell reference, a range of cell references, or a text string typed directly into the formula. With this flexibility, we can quickly combine the above phone numbers into one string by using the A2 to D2 range as the argument of the CONCAT function, which CONCATENATE cannot do.

=CONCAT(A2:D2)

CONCAT maintains all the other features of CONCATENATE, including the need to manually insert individual delimiters such as spaces, ampersands, commas, etc., between strings if needed.

For example, to place the area code within dashes in the above example, we would enter:

=CONCAT(A2,"(",B2,")",C2:D2)

Limitations of CONCATENATE and CONCAT

A major disadvantage of both the CONCAT and CONCATENATE functions is that delimiters have to be entered individually, even if the delimiter between each argument is the same. This can be very time-consuming, difficult to read, and is subject to errors.

TEXTJOIN

My personal favorite of the three string-joining functions is TEXTJOIN. TEXTJOIN was rolled out with Excel 2019 and is a real time-saver. It has three required arguments.

The syntax is:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Argument definitions

• delimiter (required) is a character or characters which will separate each argument, entered within double quotes.
• ignore_empty (required) is a setting that tells Excel what to do with empty cells. If TRUE, empty cells are ignored. If FALSE, a space is displayed to represent the empty cell in the result.
• text1 (required) is a text string, or array of strings.
• [text2] is an optional text string or array of strings. A total of 252 text arguments are permitted, with a maximum of 32,767 characters.

The most obvious advantage of TEXTJOIN is that if the delimiters between strings are identical, it eliminates having to type the same delimiter over and over.

Therefore it is quite possible that only three arguments will be required, even where the desired output is stretched across several strings. This is because the delimiter was entered once, at the beginning of the formula, and the third argument (text1) can be a range.

In the following example, each customer’s title (if any), first name, last name, and the elements of their address are to be joined — each separated by a space. We also want to insert a line break after the name, so the CHAR(10) unicode character is entered after the reference to cell C2. 

=TEXTJOIN(" ",TRUE,A2:C2,CHAR(10),D2:H2)

Notice that the second argument is set to TRUE so that if Excel comes across any empty cells, it does not create an extra space in the output cell. Column I has been set to wrap text so that the result of the line break is displayed. 

In order to get a similar result from the CONCATENATE function, the entry would have been:

=CONCATENATE(A2," ",B2," ",C2,CHAR(10),D2," ",E2," ",F2," ",G2," ",H2)

With the CONCAT Excel function, the entry would be:

=CONCAT(A2," ",B2," ",C2,CHAR(10),D2," ",E2," ",F2," ",G2," ",H2)

Though ranges are permitted with the CONCAT function, it provides no advantage here since delimiters are required between each string. That would mean a total of 15 arguments using the older functions, compared with five arguments in TEXTJOIN.

Furthermore, CONCATENATE and CONCAT cannot ignore empty cells so an extra delimiter is created for each occurrence of blank cells, for example, with the omission of the title or some portions of the address in the examples above. 

The ignore_empty argument in TEXTJOIN handles this perfectly so that unnecessary delimiters are removed from the results.

Concatenation operator (&)

It is possible to concatenate in Excel without the use of functions. This is done with the use of the ampersand (&) symbol as a concatenation operator.

Let’s compare using the concatenation operator with using the CONCAT function. We’ve chosen CONCAT because it is the newer version of CONCATENATE, but the same principle would have applied to CONCATENATE.

The format here is that the & operator is entered after each cell reference and value, including the space character supplied within double quotes. The results in column D are identical to column E’s use of CONCAT, shown below.

Of course, the concatenation operator doesn’t accept cell ranges, and the disadvantage of empty cells is only solved by using the TEXTJOIN Excel function.

TEXTJOIN allows you to give a more uniformed finish in the output cells, even if some elements are missing.

Learn more

Have you tried using all four methods to concatenate? Which one do you prefer? 

Start your journey with the free Excel in an Hour course today!

Free Excel crash course

Learn Excel essentials fast with this FREE course. Get your certificate today!

Start free course