CONCATENATE in Excel

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:

The function accepts the following arguments:

  • Text1: This is the first cell reference, text value or number to be joined.Text2: Thisis the second cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more, text value or number to be joined.

 “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.

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.

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.

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)”

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

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.

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 functionVLOOKUP FunctionThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more 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.

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

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

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.

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.

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.

Concatenate Excel Function Video

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–

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)”

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.

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 DateConcatenate DateTo concatenate a date with other values in Excel, we can use the & operator or the built-in concatenate function. For example, if we use =“ABC”&NOW(), the output will be ABC14/09/2019.read moreOpposite of Concatenate in ExcelOpposite Of Concatenate In ExcelThe opposite of concatenate in excel helps the users split the values of a specific column into separate columns, unlike the Concatenate Strings in Excel. It can be done either by using Text functions or through a Text to Column Option.read moreConcatenate StringsConcatenate StringsConcatenation of Strings is accomplished using Excel’s two concatenate methods, the & operator and the concatenate inbuilt function. read moreVBA CONCATENATE VBA CONCATENATE In VBA, concatenation refers to joining two values or strings together with the “&” symbol (ampersand operator). We must include spaces when using the & operator, else VBA will interpret it as long.read more