Google Sheets: Concatenate Function

Combine multiple cells of data in a new cell

Illestration of concatenate function in Excel

Lifewire / Tim Liedtke 

In Google Sheets, concatenation usually refers to combining 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.

CONCAT and CONCATENATE do similar functions, but the older version supports ranges and more cells. In a CONCAT function, you can only combine two cells, but the formatting is the same.

These instructions use the Google Sheets app for iOS. The desktop version may have differences.

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 you're using. This is usually in all-caps, but that isn't necessary. Some examples are SUM, ROUNDUP, and PRODUCT.
  3. A set of parentheses: (). If your function includes work on a set of numbers in your spreadsheet, they 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, still has the parentheses, but they're 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 bits of data in your spreadsheet. They can be individual cells or ranges of them, like entire rows or columns. The rules for a valid function is that you present at least one data point (or "argument," as Google Sheets calls them), and you have to separate each point or range with a comma.

A valid CONCATENATE function may look like this:

=CONCATENATE(A1,B2:B5,A2)

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

If your function has a range that includes several rows and columns, it will list their contents in order from left to right and top to bottom, as you would read them.

How to Add Spaces to a CONCATENATE Function

Concatenation doesn't leave a blank space between words, but you can build them into the formula. Wherever you want a space, insert a set of double quotation marks with a space between them. 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 already, this won't affect anything. But if you use numbers, you won't be able to include the result in math functions such as SUM and AVERAGE. That's because math functions ignore text.

How to Enter the CONCATENATE Function

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

  1. Key the information you want to concatenate, and then tap the cell where you want the combined data to appear.

    Concatenate in Google Sheets
  2. Type the equal sign ( = ), and then type CONCATENATE.

    Suggestions will appear above the keyboard as you type, so you won't have to key the entire word.

    Concatenate function in Google Sheets
  3. Tap the cells you want to combine in the order in which you want them. You can also drag and select a range. Google Sheets will automatically enter commas to separate strings of data.

    In the final result, strings will appear in the order in which you select them.

  4. To add a space, put the cursor between the two entries you want to separate, and then type two double quotation marks with a space between them. 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. You can use the desktop version or add the space to the terms you're concatenating, if possible.

    Concatenate function in Google Sheets
  5. Tap Return or the check mark to run the function.

    Concatenate function in Google Sheets
  6. Your concatenated data will appear in the cell.

    Concatenate function in Google Sheets
  7. Repeat the process for all of 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.

    Concatenate function in Google Sheets
  8. If you change the text in one of the cells in your formula, the concatenation result will also update.

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.