Concatenate Excel Function

CONCATENATE in Excel

The CONCATENATE function in Excel helps the user concatenate or join two or more cell values which may be in the form of characters, strings or numbers. For example, the formula “=CONCATENATE(A4,“ ”,B4)” joins the cells A4 and B4 containing the values “Rock” and “Band” respectively. It returns the string of cell A4, followed by a space character, and the string of cell B4. Hence, the output is “Rock Band.”

With CONCATENATE, the content of different cells is combined together and placed in a single cell. The CONCATENATE function in Excel acts as a substitute for the “&” operator and joins the values. The usage of the “&” operator makes the formula’s complex. In contrast, the CONCATENATE formulas are simple and easy to understand.

Syntax

The syntax of the function is shown in the following image:

Concatenate Formula

The function accepts the following arguments:

 “Text1” is the required argument. “Text2” and the subsequent arguments are optional and can be added based on the user’s requirement.

Note: The optional arguments are enclosed within the square brackets.

How to Use the CONCATENATE Function in Excel?

Let us understand the working of the CONCATENATE function with the help of the following examples.

You can download this CONCATENATE Function Excel Template here – CONCATENATE Function Excel Template

Example #1

The following table shows the first and the last names in columns A and B, respectively. We want to join the first name and the last name together so that the full name is displayed in the column C. Use the CONCATENATE function of Excel.

First NameLast Name
AdamCook
AlbertParker
BillyCollins
ClarkRogers
ElvisCooper
FredPeterson
SophiaWoods
EmmaBarnes
OliviaFord
EmilyFreeman

We apply the following CONCATENATE excel formula to join the names of cells A2 and B2:

“=CONCATENATE(A2,B2)”                                                                                       

The output of the formula is shown in cell C2.  The first and the last name are joined without a space between them. As a result, the readability of the full name in cell C2 is affected.

CONCATENATE Example 3

To introduce a space between the first and the last names, let us pass another argument to the CONCATENATE formula (between the text values A2 and B2).

The second argument is a string literal with a space enclosed within the double quotes (“ ”).In a string literal, characters exist at their literal value.

We apply the following formula in cell C2, as shown in the succeeding image.

 “=CONCATENATE(A2,“ ”,B2)”

CONCATENATE Example 2

The output is shown in the following image. A space has been inserted between the first and last names in cell C2.

CONCATENATE Example 2

Example #2

The following image shows three tables titled “table 1,” “table 2,” and “table 3.”  All the three tables consist of the names of ten employees of an organization. In addition, “table 1” and “table 2” show the employee IDs and the native places (cities), respectively.

The names of “table 2” and “table 3” are not according to the sequence given in “table 1.”

For every employee, we want to join the IDs and the cities with a hyphen in between. The resulting concatenated values should be listed in the second column of “table 3” which displays question marks.

CONCATENATE Table 1,2,3

Since “table 2” and “table 3” do not show the names in the order listed in “table 1,” we cannot pass the reference values directly. Moreover, passing direct values is a time-consuming task which requires the matching of entries.

Instead, let us use the VLOOKUP function to look up the employee ID and the city. The values thus returned can be concatenated.

We apply the following formula to cell I2. The VLOOKUP function is nested within the CONCATENATE excel function.

“=CONCATENATE(VLOOKUP(H2,$A$1:$B$11,2,0),“ -” ,VLOOKUP(H2,$D$1:$E$11,2,0))”

The output of the formula is shown in cell I2 of the following image.

CONCATENATE Example 5

Drag the formula to the remaining cells of column I, as shown in the following image.

CONCATENATE Example 6

The final output is displayed in column I of “table 3.”

CONCATENATE Example 7

The “#NAME” Error in CONCATENATE Excel Function

When the user passes an argument apart from the reference value in the formula, it should be enclosed within double quotes.  The arguments within the double quotes are the string literals written in the C++ programming language. String literals are used in MS Excel and other office packages.

If the string is not enclosed within double quotes, the CONCATENATE excel function does not recognize it. Hence, it returns the “#NAME” error.

The succeeding image shows the error returned by the CONCATENATE formula in cell C2. This takes place because the second string argument is not enclosed in double quotes.

CONCATENATE Example 4

In the CONCATENATE function, the result of formulas can be concatenated. For this, the different formulas must be passed as an argument to the function.

Limitation of the CONCATENATE Excel Function

With the CONCATENATE excel function, we can combine all the text values of a list into a single string. The function accepts the text values as arguments.

The limitation of the CONCATENATE function is that we cannot pass a range of values as an argument. As a result, the user has to enter individual cell references one by one in the function. This is a time-taking and challenging task for the user.

If we pass a range of values, the CONCATENATE function picks up the cell values of the row to which the formula has been applied. The same is displayed in the following image.

Example 15

To overcome this limitation, a new function, TEXTJOIN has been introduced in the latest version of the Excel. It concatenates the values on specifying the range (like A2:A14), rather than individual cell references.

The syntax of the TEXTJOIN function is stated as follows:

“=TEXTJOIN(delimiter, ignore_empty, range)”

Where,

  • Delimiter: It refers to the separators like “tab,” “semicolon,” “comma,” and “space” used between the values to be combined.
  • Ignore_empty: It checks if the empty cells are to be ignored or added to the result. If it returns “true,” the empty cells are ignored.  If it returns “false,” the empty cells are included in the result.
  • Range: It is the range of values to be concatenated.

All the three arguments “delimiter”, “ignore_empty,” and “range” are mandatory.

Frequently Asked Questions (FAQs)

1. What is CONCATENATE in Excel?

The CONCATENATE excel function allows users to combine the text values of different cells and place them in a single cell.
The syntax of the CONCATENATE formula is stated as follows:
“=CONCATENATE(text1,[text2], …)”

Where,
• “Text1” refers to the first value to be joined. This value can be a cell reference, text value or number.
• “Text2” refers to the second value to be joined. This value can also be a cell reference, text value or number.

“Text1” is a required argument, while the subsequent arguments are optional.

For example, the following formula combines the text values of cells A2 and B2: “=CONCATENATE(A2, B2)”

2. How to concatenate strings containing a comma in Excel?

The steps to concatenate strings containing a comma are listed as follows:

1. Select a blank cell in which you want the output of the CONCATENATE excel function.
2. Enter the following formula in the blank cell.
“ =CONCATENATE(C1,“,”,E1)”
Where,
• C1,E1 – It refers to the cells containing the values to be joined.
• “,” – It refers to the separator or the delimiter that separates values. It is always enclosed within double quotes.
3. Press the “Enter” key. The output consisting of the concatenated string appears in the blank cell.

3. How do you concatenate text and dates in Excel?

The steps to concatenate text and dates are listed as follows:

1. Select a cell in which you want the output of the CONCATENATE formula.
2. Enter the following formula in the selected cell:
“ =CONCATENATE(D2, “ ”,TEXT(E2, “mm/dd/yyyy”)”
Where,
• D2 is the cell that contains the text value to be concatenated.
• E2 is a cell that contains the date. It is to be combined with the cell D2.
• mm/dd/yyyy refers to the specific date format.
3. Press the “Enter” key.
The output shows the concatenated text and date in a single cell. The date is displayed in the desired format.

Concatenate Excel Function Video

 

Recommended Articles

This has been a guide to CONCATENATE function in Excel. Here we discuss how to use CONCATENATE in Excel and its formula, along with examples and downloadable templates. You may also look at these useful functions in Excel–

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Comments

  1. Oobosae says

    Great. Thanks for sharing.

Leave a Reply

Your email address will not be published. Required fields are marked *