How to use the Excel CONCATENATE Function to Combine Cells

Combine the content of two or more cells in Excel

Red and green rope knotted in unity
Learn how to quickly combine data in Excel.

 Getty Images/Hero Images

In Microsoft Excel, concatenation generally refers to combining the contents of two or more ​cells in a worksheet into a third, separate cell using either; this process can be accomplished by either using the CONCATENATE function or the concatenation operator. Take a look at the different ways you can combine cells in Excel, potentially improving your current workflow.

Understanding the Excel CONCATENATE Function

Screenshot of Excel showing Concatenate examples.

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

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, need the space so it must be included in the concatenation formula, as shown in rows four, five, and six of the accompanying image.

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.

How to Use the Excel CONCATENATE Function

Screenshot of Excel showing Concatenate formula builder.

Although it is possible to type the complete function in manuallymany 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 appears in the formula bar above the worksheet.


Displaying the Ampersand in Concatenated Text

Screenshot of Excel showing use of Concatenate Function with ampersands.

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.

=A6 & " & " & B6 

For example, the formula in cell D6 could be replaced with the above formula to achieve the same results.