Excel CONCATENATE Function

Combine the content of two or more cells in Excel

The term concatenate means to combine or join together two or more separately located objects in a new location with the result being treated as a single entity.

In Excel, concatenation generally refers to combining the contents of two or more ​cells in a worksheet into a third, separate cell using either:

  • The CONCATENATE function as in rows one, four, and six in the accompanying image
  • Using the concatenation operator, which is the ampersand sign &, as in rows two and five above.

Combining Cells in Excel

Excel CONCATENATE Function
© Ted French

Neither method of concatenation automatically leaves a blank space between words, which is fine when joining two parts of a compound word like Baseball into one or combining two series of numbers like 123456.

Joining first and last names or an address, however, needs the space so a space must be included in the concatenation formula, as shown in rows four, five, and six of the accompanying image.

The CONCATENATE Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The syntax for the CONCATENATE function is:

= CONCATENATE ( Text1, Text2, ... Text255 )

Text1 (required) can be actual text such as words or numbers, blank spaces surrounded by quotation marks, or cell references to the location of data in a worksheet.

Text2, Text3, ... Text255 (optional up to 255 text entries) can be added to the CONCATENATE function to a maximum of 8,192 characters – including spaces. Each entry must be separated by a comma.

Concatenating Number Data

Even though numbers can be concatenated, as seen in row six of the example image, the result 123456 is no longer considered a number by the program but is now seen as text data.

The resulting data in cell C7 cannot be used as arguments for certain math functions such as SUM and AVERAGE. If such an entry is included with a function's arguments, it is treated like other text data and ignored.

One indication is that the concatenated data in cell C7 is aligned to the left, which is the default alignment for text data. The same result occurs if the CONCATENATE function is used instead of the concatenate operator.

Excel's CONCATENATE Function Example

As seen in the image, this example combines the data found in separate cells A4 and B4 of a worksheet into a single cell in column C.

Since the concatenate function does not automatically leave a blank space between words or other data, a space is added to line Text 2 of the dialog box using the space bar on the keyboard.

Entering the CONCATENATE Function

Although it is possible to type the complete function in manually, such as =CONCATENATE(A4," ",B4)many people find it easier to use the dialog box to enter a function's arguments, since the dialog box takes care of entering brackets, commas and, in this example, the quotation marks surrounding the blank space.

The steps below cover entering the function using the dialog box into cell C2.

  1. Click on cell C2 to make it the active cell.
  2. Click on the Formulas tab.
  3. Choose Text Functions from the ribbon to open the function drop-down list.
  4. Click on CONCATENATE in the list to bring up the function's dialog box.
  5. Click on line Text 1 in the dialog box.
  6. Click on cell A4 in the worksheet to enter that cell reference into the dialog box.
  7. Click on line Text 2 in the dialog box.
  8. Press the Space Bar on the keyboard to add a space to line Text 2. Excel adds double quotation marks around the space.
  9. Click on line Text 3 in the dialog box.
  10. Click on cell B4 in the worksheet to enter that cell reference into the dialog box.
  11. Click OK to close the dialog box and return to the worksheet.

The concatenated name Mary Jones appears in cell C4.

When you click on cell C4 the complete function =CONCATENATE(A4," ",B4) appears in the formula bar above the worksheet.

Displaying the Ampersand in Concatenated Text Data

There are times where the ampersand character is used in place of the word and, such as in company names as shown in row six of the example image.

To display the ampersand as a text character rather than have it act as the concatenation operator, you surround it in double quotation marks like other text characters, as shown in the formula in cell D6.

It should be noted that in this example, spaces are present on either side of the ampersand to separate that character from the words on either side. To achieve this result, you enter space characters on either side of the ampersand inside the double quotation marks in this fashion: " & ".

Similarly, if a concatenation formula that uses the ampersand as the concatenation operator is used, the space characters and the ampersand surrounded by double quotes must also be included to have it appear as text in the formula results.

For example, the formula in cell D6 could be replaced with the formula

=A6 & " & " & B6

to achieve the same results.