Excel CONCATENATE Function

of 01

Combine Cells of Text Data in Excel

Excel CONCATENATE Function
Excel CONCATENATE Function. © Ted French

Concatenation Overview

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  - rows one, four, and six in the image above;
  • using the concatenation operator - the ampersand ( & ) - rows two and five above.

Adding Spaces to Concatenated Text

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.

When joining first and last names or an address, however, needs the space so a space must be included in the concatenation formula - rows four, five, and six above.

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 the row six above - 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 - the default alignment for text data. The same result would occur if the CONCATENATE function was used instead of the concatenate operator.

Excel's CONCATENATE Function Example

As seen in the image above, this example will combine the data found in separate cells in 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 will be 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 just 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 will add 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;
  12. The concatenated name Mary Jones should appear in cell C4;
  13. 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 above.

To display the ampersand as a text character rather than have it act as the concatenation operator, it must be surrounded 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 in order to separate that character from the words on either side. To achieve this result, space characters are entered 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 in order 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.