Which of the following types is the concatenate function?

Concatenation just means to join two things together. And in Excel, you can use the CONCAT function to join data from multiple cells into one cell. Let's see how it works.

Syntax of the CONCAT function

The general syntax of the CONCAT function is:

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

Where the arguments are:

• text1 is a required argument that can be a string or an array of strings (like a range of cells).
• [text2, ...] identifies the optional arguments that follow. There can be up to 253 text arguments, and each one can also be a string or an array of strings (such as a range of cells).

How to use the CONCAT function in Excel

To use the CONCAT function, you will need to click on the cell in which you want the result to appear, and type =CONCAT( there.

After that you can click on the cells, or range of cells, you want to concatenate (or join) together – you will need to keep the Ctrl button pressed to add multiple arguments.

Like you can see in these screenshots, we are writing =CONCAT( in the cell D1 and then clicking on the cells A1 and B1 while keeping Ctrl pressed. After that, pressing Enter will give the result in the cell D1.

If you want to add text in between the different cells' content, for example a space, you can click on a cell, then type a semicolon and the text you want to add in between quotes, then another semicolon, and finally click on the next cell:

In this case you would write =CONCAT( in cell D1, then click on cell A1, type a semicolon, then type the space surrounded by strings, " ", followed by another semicolon, and finally click on cell B1. This will result in "Leonardo da Vinci" in cell D1.

Note: You can concatenate together up to 32,767 characters, which is the cell limit. If the resulting string is longer than 32,767 characters the function will output a #VALUE! error.

Examples

The official Microsoft documentation has three helpful examples of how to use the CONCAT function. Let's examine them.

CONCAT Example 1:

=CONCAT(B:B; C:C)A'sB'sa1b1a2b2a4b4a5b5a6b6a7b7

If you want to follow along, copy this table and paste its data in cell A1 of a new Excel sheet.

This function allows for whole column reference, so in the formula CONCAT1 you are concatenating the whole column B (CONCAT2) followed by the whole column C (CONCAT3), so the result is CONCAT4 – the empty cells do not contribute to the end result.

CONCAT Example 2:

=CONCAT(B2:C8)A'sB'sa1b1a2b2a4b4a5b5a6b6a7b7

To follow along, copy this table, and paste it in the A1 cell of an empty Excel sheet.

In this case, it is the cells in the range CONCAT5 (the range highlighted in the picture) that are being concatenated together, and the formula in the A1 cell will result in CONCAT6. Also in this case the empty cells do not contribute to the result.

Note: The cells in a range are read row by row, so the cells are concatenated in the order: CONCAT7 (with B8 and C8 being empty, so not contributing to the end result).

CONCAT Example 3

This example has various examples in it, all with the general theme of joining cells with other text. This example illustrates well how you can do a lot of things with the Concat function.

Let's take a look.

DataFirst NameLast namebrook troutAndreasHauserspeciesFourthPine32FormulaDescriptionResultCONCAT8Creates a sentence by joining the data in column A with other text.Stream population for brook trout species is 32/mile.CONCAT9Joins three things: the string in cell B2, a space character, and the value in cell C2.Andreas HauserCONCAT0Joins three things: the string in cell C2, a string with a comma and a space character, and the value in cell B2.Hauser, AndreasCONCAT1Joins three things: the string in cell B3, a string consisting of a space with ampersand and another space, and the value in cell C3.Fourth & PineCONCAT2Joins the same items as the previous example, but by using the ampersand (&) calculation operator instead of the CONCAT function.Fourth & Pine

To follow along you can also copy this table in the A1 cell of an empty Excel sheet.

Stream population for brook trout species is 32/mile.

The first formula shown in this example joins together the text of three cells (A2, A3 and A4) with a few strings to form a longer sentence.

So, the result is given by the combination of the string CONCAT3 with the content of the cell A2, so CONCAT4, which is then followed by a space (" "), the content of cell A3 (CONCAT6), the string CONCAT7, the content of cell A4 (CONCAT8), and finally, the string CONCAT9.

All together it results in the string text10.

(For the following screenshots I have hidden the rows of the formulas I have already talked about).

Andreas Hauser

In this example, the formula text11 joins together the string from the cell B2, a space " " and the string from the cell C2, resulting in text13.

Hauser, Andreas

From this example you can see that the order in which the cells are written in the CONCAT function arguments matter. The formula CONCAT0 joins together the strings from cell C2, a comma and a space text16 and from cell B2, resulting in text17.

The strings from cells C2 and B2 have been joined in a different order because they have been written in a different order in the arguments of the method.

Fourth & Pine

You can join together any kind of screen. See here where the formula CONCAT1 joins the string from cell B3 with the string from cell C3 putting in the middle of the two the string with a space, an ampersand and another space, text19 resulting in [text2, ...]0.

Fourth & Pine (II)

This example shows an alternative method of concatenating strings: using the operator [text2, ...]1 you can write the list of strings you want to join separated by an ampersand ([text2, ...]1). So CONCAT2 gives the same result as CONCAT1.

Note: You can use the [text2, ...]1 operator only for single cells, it doesn't work for ranges.

And that's it! Now you know how to use the CONCAT function in Excel. Thanks for reading!

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

---

Ilenia Magoni

Moderator and staff author for freeCodeCamp.

---

If you read this far, tweet to the author to show them you care. Tweet a thanks

Learn to code for free. freeCodeCamp's open source curriculum has helped more than 40,000 people get jobs as developers. Get started

What type of function is CONCATENATE?

What is function concatenation?

Which of the following function is used to CONCATENATE strings?

Which of the following is concatenating operator in Excel?