Google Sheets: Concatenate Function

Combine multiple cells of data in a new cell

Screenshot of Google Spreadsheets CONCATENATE Function

Ted French

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

In Google Sheets, concatenation usually refers to combining the contents of two or more cells in a worksheet into a third separate cell using either:

  • The CONCATENATE function; or
  • The concatenation operator – the ampersand ( & ).
of 03

About the CONCATENATE Function Syntax

The examples in this tutorial refer to elements in the image that accompanies this article.

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 (string1, string2, string3, ...)

  • String1 is required. It is the first data entry to be concatenated by the function. This argument can be words, a single cell reference to the location of the data in the worksheet, a range of cell references, blank spaces, or numbers.
  • String2, String3, ... No limit is given to the number of arguments that can be added to the function. Each argument must be separated by a comma.

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, the result needs the space so a space must be included in the concatenation formula. It is added with a double parenthesis followed by a space and another double parenthesis (" ").

Concatenating number data

Even though numbers can be concatenated, 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 of this 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 was used instead of the concatenate operator.

of 03

Entering the CONCATENATE Function

Google Sheets does not use dialog boxes to enter a function's arguments as can be found in Excel. Instead, it has an auto-suggest box that pops up as the name of the function is typed into a cell.

Follow the steps in this example to enter the CONCATENATE function into Google Sheets. Before you start, open a new spreadsheet and enter the information in the seven rows of columns A, B, and C as shown on the image accompanying this article.

  1. Click on cell C4 of the Google Sheets spreadsheet to make it the active cell.
  2. Type the equal sign ( = ) and begin to type the name of the function: concatenate. As you type, the auto-suggest box appears with the names and syntax of functions that begin with the letter C.
  3. When the word CONCATENATE appears in the box, click on it with the mouse pointer to enter the function name and open round bracket into cell C4.
  4. Click on cell A4 in the worksheet to enter this cell reference as the string1 argument.
  5. Type a comma ( , ) to act as a separator between the arguments. 
  6. To add a space between first and last names, type a double quotation mark ( " " ) followed by a space followed by a second double quotation mark ( " " ). This is the string2 argument.
  7. Type a second comma ( , ) separator.
  8. Click on cell B4 to enter this cell reference as the string3 argument.
  9. Press the Enter or Return key on the keyboard to enter a closing parenthesis around the function's arguments and to complete the function.

The concatenated text Mary Jones should appear in cell C4.

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

of 03

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 the example image.

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.

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.