Google Spreadsheets CONCATENATE Function

of 01

Concatenate Cells of Text Data in Google Spreadsheets

Google Spreadsheets CONCATENATE Function
Google Spreadsheets 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 Google Spreadsheets, 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 ffive 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 ( string1, string2, string3, ... )

string1 - (required) 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, ... - (optional) no limit is given on the number of arguments that can be added to the function. Each argument 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.

Steps to Entering the CONCATENATE Function

Google Spreadsheets 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.

The steps below were used to enter the CONCATENATE function into cell C4

  1. Click on cell C4 to make it the active cell;
  2. Type the equal sign ( = ) followed by the name of the function - concatenate;
  3. As you type, the auto-suggest box appears with the names and syntax of functions that begin with the letter C;
  4. When the name CONCATENATE appears in the box, click on the name with the mouse pointer to enter the function name and open round bracket into cell C4;
  5. Click on cell A4 in the worksheet to enter this cell reference as the string1 argument;
  6. After the cell reference, type a comma ( , ) to act as a separator between the arguments;
  7. Next, to add a space between the first and last names, type a double quotation mark followed by a space followed by a second double quotation mare ( " " ) - this is the string2 argument;
  8. Type a second comma separator;
  9. Click on cell B4 to enter this cell reference as the string3 argument;
  10. Press the Enter key on the keyboard to enter a closing parenthesis " ) " around the function's arguments and to complete the function;
  11. The concatenated text Mary Jones should appear in cell C4;
  12. 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.