How to Use the Concatenate Function in Google Sheets

In Google Sheets, concatenation combines the contents of two or more cells in a worksheet into a third separate cell using the CONCATENATE function or its newer version, CONCAT. Here's what the CONCATENATE function does and how to use it.

How to Write a Function in Google Sheets

A function in Google Sheets (or other spreadsheet programs like Microsoft Excel) has three parts, in this order:

1. An equals sign (=). This tells the program that you're entering a function.
2. The name of the function. This is usually in all-caps, but that isn't necessary. Some examples are SUM, ROUNDUP, and PRODUCT.
3. A set of parentheses. If the function includes work on a set of numbers in the spreadsheet, these numbers go in the parentheses to tell the program which data to use in the formula. Some functions, like NOW, which returns the current date and time, has the parentheses, but these are empty.

How to Write a CONCATENATE Function

CONCATENATE follows the format above, but it has some specific features. The general layout is:

=CONCATENATE(string 1, [string2, ... ])

The strings refer to specific data in the spreadsheet. These strings can be individual cells or ranges of cells, like entire rows or columns. The rules for a valid function are that you present at least one data point (or argument), and each point or range is separated with a comma.

A valid CONCATENATE function may look like this:

=CONCATENATE(A1,B2:B5,A2)

When Sheets runs the function, the result displays every entry in the cells the formula mentions arranged in order.

How to Add Spaces to a CONCATENATE Function

Concatenation doesn't leave a blank space between words. However, you can build spaces into the formula. Wherever you want a space, insert a set of double quotation marks with a space between the quotation marks. The sample function above, with a space between the first two strings, would look like this:

=CONCATENATE(A1," ",B2:B5,A2)

Limits to Concatenating Numbers

Google Sheets formats the result of a CONCATENATE function as text. If your entries are text, this doesn't affect anything. But, if you use numbers, you can't include the result in math functions such as SUM and AVERAGE. That's because math functions ignore the text.

How to Enter the CONCATENATE Function

Google Sheets doesn't use dialog boxes like Excel's to enter the function arguments. Instead, it has an auto-suggest box that appears as you type the name of the function into a cell.

1. Enter the information you want to concatenate, then select the cell where you want the combined data to appear.

2. Type the equal sign ( = ), then type CONCATENATE.

   Suggestions appear above the keyboard as you type, so you don't have to enter the entire word.

3. Tap the cells in the order you want to combine the cells. Or, drag and select a range. Google Sheets automatically enters commas to separate strings of data.

   In the final result, strings appear in the order in which you selected the cells.

4. To add a space, put the cursor between the two entries you want to separate, then type two double quotation marks with a space between each. This element lets you add any text you want to the function.

   The quotation marks on the default iOS keyboard don't work for this function. Use the desktop version or add the space to the terms you're concatenating, if possible.

5. Tap Return or the checkmark to run the function.

6. The concatenated data appears in the cell.

7. Repeat the process for all the cells you want to concatenate.

   If you use absolute values with dollar signs in the formula, you can use autofill for specific rows and columns.

8. If you change the text in one of the cells in your formula, the concatenation result updates.

You can also access CONCATENATE through the Function button next to the text box on iOS or in the upper-right corner of the screen on the desktop. The mobile version looks like the letters fx, and the desktop version looks like the Greek letter sigma (∑). CONCATENATE is under the Text heading in the Function menu.