Excel CONCAT Function – Join Values in Cell Range

The CONCAT excel function is a newly introduced excel formula that is used to join values in multiple cells in excel.

This excel function is available in Microsoft Excel 2019 and Office 365 versions. It has enhanced the functionality of the existing CONCATENATE excel formula.

The CONCAT excel formula is more useful as compared to the CONCATENATE function when you want to join 2 or more texts in an excel cell range.

Let us deep dive the CONCAT formula in excel along with examples. Here we go 😎

When to Use CONCAT Function in Excel

Excel CONCAT formula is used to combine/join two or more texts, numbers, strings, values, etc. in excel including the cell range.

The output of this function is a combined text/value.

Syntax and Arguments

CONCAT(text1, [text2, … text_n])

The CONCAT formula accepts 255 arguments in total, with one mandatory and other optional arguments.

  • text1 – Enter the first text that you want to join.
  • [text2] – Enter the second text to join.

In a similar manner, you can insert up to 255 input arguments to join in the CONCAT function, each separated by a comma.

CONCAT Function is not available in the older excel versions (Excel 2016 & before). To join text in cells in these versions, use the CONCATENATE function.

How To Use CONCAT Function in Excel – Examples

In this section of this article, we would learn how to use the CONCAT Formula in excel with the help of examples.

Ex. 1 # Basic Example of Excel CONCAT Formula

In the below image, cells A2 contains the email address and cell B2 contains the domain.

Email ID and Domain in Excel

Suppose you want to join the email address and the domain in excel using CONCAT excel formula with @ symbol in between.

To do so, use the below formula:

=CONCAT(A2,"@",B2)
Joining Email ID & Domain with @ in between Excel

As a result, you would get the result as – “[email protected]“.

Infographic - CONCAT Formula Function in Excel

Note that, when you use anything as text, it must be put within double-quotes. In the above example, the symbol @ is used as a text, hence we have put it in double-quotes.

Ex. 2 # Using CONCAT Formula to Join Values in Cell Ranges

One of the biggest advantage of the new CONCAT function over the CONCATENATE formula is that we can use the cell range as CONCAT input argument.

In the below image, column A contains a list. Suppose you want to combine these strings into one cell.

Using Cell Range - CONCAT Function Example

To combine the alphabets A, B, C, D, etc. in a cell range into a single cell, use the cell range as formula input argument:

=CONCAT(A2:A11)
Result of CONCAT Function Using Cell Ranges

As a result you would get the following output – ABCDEFGHIJ.

How Is CONCAT Different from CONCATENATE

The example number 2 in the above section is classic example to show the difference between CONCAT and CONCATENATE function.

If in case you use the CONCATENATE function to achieve the above task, you have manually select each of the cells, like this.

=CONCATENATE(A2,A3,A4,A5,A6,A7,A8,A9,A10,A11)

This is quite tiresome and unacceptable when the list is huge. 😐

This is because the CONCATENATE function does not accept cell range as its input argument.

However in such case, CONCAT function comes like a life-saver jacket which accepts cell range and makes the formula shorter and quicker to apply. 😎

Thank You 🙂

Leave a Comment