CONCAT()

This article is also available in: Bahasa Malaysia Bahasa Indonesia

Excel’s CONCAT() function allows us to combine text from different cells to make one text value.

CONCAT(A1, " ", B1, " ", C1,..........)

You can also use CONCAT() to combine text that you provide.

CONCAT("This", " ", "is", " ", "an", " ", "example")

How is CONCAT() useful?

CONCAT() is useful when you have a list of values in different cells and you want to quickly combine them into one value in a different cell.

As an example, if you have a list of contacts in a worksheet with Salutation, First Name and Last Name, and you want to combine these into one.

To start with, in cell G2, type in =CONCAT( then:

1. select cell A2,
2. type in a comma, then open quotation mark, a space and close quotation mark.
   This will provide a space between the salutation and first name.
3. type in another comma, and select cell B2.
4. repeat the second step and select cell C2, then close the bracket.

Your formula will now look like this:

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

The result will look like this

To get the same result for all the other contacts in the list, just copy the formula down.

CONCAT() and numerical values

CONCAT() will treat numbers in cells as text.

In our list of contacts above, we have a column for age of the contact.

Using that, we can combine the name and age into a statement as shown below:

The steps to create the formula are the same, except now, add cell E2, and type in ” is ” as well as ” years old.” as shown below:

=CONCAT(G2, " is ", E2,  " years old today.")

You can include formulas in CONCAT().

In our list of contacts, we have a column with year of birth, column D.

Using this, instead of selecting the age column, we can calculate the age of the person and include it in CONCAT().

Here, we use YEAR() to get the current year and deduct it from the year of birth.

=CONCAT(G2, " is ", YEAR(TODAY())- D2,  " years old today.")

This article is also available in: Bahasa Malaysia Bahasa Indonesia