How to use the Excel CONCATENATE Function to Combine Cells

Combine the content of two or more cells in Excel

By

Updated on November 4, 2019

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.

A screenshot showing examples of the Excel CONCATENATE function

The syntax for the CONCATENATE function is:

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

Text1 (required): The first item to be combined. It can be 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): The other items to be combined. 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 adds 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. When joining first and last names or an address, include the space in the concatenation formula, as shown in rows four, five, and six of the example image.

Concatenate Number Data

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

The result shown in cell C7 cannot be used as arguments for certain math functions such as SUM and AVERAGE. If this type of 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

Although it is possible to enter the complete function manually, it may be easier to use the dialog box to enter a function's arguments. The dialog box takes care of entering brackets, commas and, in this example, the quotation marks surrounding the blank space.

Here's how to enter the function into cell C4 using the dialog box:

Select cell C4 to make it the active cell.
Go to the Formulas tab.
Select Text to open the function drop-down list.
Choose CONCATENATE. In Excel 2019 and Excel 2016, choose CONCAT.

The CONCATENATE function has been replaced with the CONCAT function. The CONCATENATE function is still available in Excel 2019 and Excel 2016 for backward compatibility, but may not be available in future versions of Excel.
In the Function Arguments dialog box, place the cursor in the Text1 text box.
Select cell A4 in the worksheet to enter that cell reference.
Place the cursor in the Text2 text box.
Press the Space Bar on the keyboard to add a space. Excel adds double quotation marks around the space.
Place the cursor in the Text3 text box.
Select cell B4 in the worksheet to enter that cell reference.
Select OK to complete the function.

The concatenated name Mary Jones appears in cell C4.

=CONCATENATE(A4," ",B4)

When you select cell C4, the complete function appears in the formula bar above the worksheet.

Display the Ampersand in Concatenated Text

There are times where the ampersand character is used in place of the word 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, surround it with double quotation marks like other text characters, as shown in the formula in row 6.

Screenshot of Excel showing use of Concatenate Function with ampersands

In this example, spaces are placed on either side of the ampersand to separate that character from the words on either side. To achieve this result, enter space characters on either side of the ampersand inside the double quotation marks like this:

" & "

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