Ultimate Guide to CONCATENATE in Google Sheets

Leverage this ultimate guide to CONCATENATE in Google Sheets to learn how to combine values from separate cells easily.

Manually combining cell values in Google Sheets is time-consuming and error-prone, especially if you use long, complex formulas to merge them.

Fortunately, the CONCATENATE function in Google Sheets allows you to combine cell values automatically.

In the following guide, we’ll cover everything you need to know about the CONCATENATE function in Google Sheets, based on step-by-step walkthroughs and tutorials.

Video Walkthrough: How to Use CONCATENATE in Google Sheets

What is the CONCATENATE Function in Google Sheets?

The CONCATENATE function in Google Sheets joins or appends data strings, allowing you to combine values from multiple cells.

CONCATENATE can help organize your spreadsheets, make your data easier to understand, simplify your data management, and much more.

To get a sense of how the CONCATENATE function works, let’s break down its syntax.

CONCATENATE Function Syntax

The CONCATENATE function’s syntax is as follows:

CONCATENATE(string1, [string2, …])

Where the parameters are:

• string1 refers to the first value you want the formula to return.
• string2, string3 … are additional values appended to the formula’s sequence.

The syntax for a CONCATENATE formula with a delimiter is:

=CONCATENATE(string1,”delimiter”,string2,”delimiter”,string3…)

Here’s what each parameter means:

• string1,string2,string3… are the cell references.
• delimiter is what you add between the cell values (enclosed in quotation marks) to separate your data strings.

Benefits of the Google Sheets CONCATENATE Function

The CONCATENATE function offers several benefits, including the following:

• Combining text strings. The CONCATENATE function allows you to combine multiple text strings into one string. This is useful for creating custom labels, headers, and text-based content.

• Increased productivity. CONCATENATE formulas can save you time and increase productivity by reducing manual string combinations.

• Improved data organization. Organizing and consolidating data into a single cell is simple with the CONCATENATE function. This makes it easier to harness and analyze your Google Sheets data.

• Better efficiency. You can combine a CONCATENATE formula with other Google Sheets functions to streamline your data workflows.

The CONCATENATE function in Google Sheets helps improve your data aggregation, organization, and presentation in many different ways.

CONCATENATE in Google Sheets: 4 Methods Based on Real Use Cases

Here are some common use cases of the CONCATENATE function in Google Sheets.

Method 1: Use a Google Sheets CONCATENATE Formula with Spaces

We’ll need a sample dataset before showcasing a use case for CONCATENATE in Google Sheets.

Let’s use the Sales Accounts data that was pulled from HubSpot into Google Sheets via Coefficient.

Coefficient automatically imports data from any system into Google Sheets. The app connects Google Sheets to any business system in one click, imports data into your spreadsheet instantly, and automates your work, including data updates.

This guide on how to connect HubSpot to Google Sheets can help you learn more about Coefficient and how you can use it to pull and sync marketing, sales, and customer data seamlessly.

Now back to the use case.

Let’s  illustrate how to CONCATENATE with spaces by harnessing the First and Last name columns (C and D) in the sample dataset.

First, add a new column beside the Last name column.

The goal is to combine the first and last names in the columns and put a space between them to separate them after merging the values.

Enter the formula below in cell E3 to concatenate the values in columns C and D.

=CONCATENATE(C3,” “,D3)

Adding the double quotes between the string1 (C3) and string2 (D3) parameters separates the values with a space. Your result will look like this:

Click on the lower right corner of cell E3 until the fill handle icon (+) appears, and drag it down to copy the formula to the entire column.

As you can see, each row’s first and last names now appear together under column E, separated by a space.

Method 2: Use CONCATENATE to Separate Values with a Comma Separator

The Google Sheets CONCATENATE function allows you to combine values from two cells and separate them with a comma.

To do this, add a comma as a separator in your formula. Your formula should look like this:

=CONCATENATE(D3,”, “,C3)

The results will show the values, separated by a comma.

Notice that our CONCATENATE formula uses D3 as string1 and C3 as string2. This is because we want the last name to appear first.

Click and drag the fill handle down to apply the formula to the rest of the cells in column E.

Method 3: Use Google Sheets CONCATENATE Formula to Add Running Numbers

You can leverage the Google Sheets CONCATENATE function to add running numbers in front of cell values.

For example, you can add a running number in front of each name in the previous dataset with this formula:

=CONCATENATE(ROW()-2,” – “,C3,” “,D3)

Adding ROW()-2 starts the numbering after the second row since the data begins on the third row.

Method 4: Combine a CONCATENATE Formula with the IF Function

You can combine the CONCATENATE function with the IF function in Google Sheets to perform concatenation and conditional operations simultaneously.

The IF function’s syntax is:

IF(logical_expression, value_if_true, value_if_false)

Free AI-Powered Tools Right Within Your Spreadsheet

Supercharge your spreadsheets with GPT-powered AI tools for building formulas, charts, pivots, SQL and more. Simple prompts for automatic generation.

You can nest CONCATENATE as a value_if argument to use it within the IF function.

Consider this example:

=IF(A1=”YES”, CONCATENATE(“You selected YES in cell A1”), CONCATENATE(“You selected NO in cell A1”))

The IF function tests the value in cell A1. If the value is “YES”, the formula concatenates the string “You selected YES in cell A1”.

If the value is not “YES”, the formula concatenates the string “You selected NO in cell A1”.

Now let’s apply the same principle to a different formula. Let’s write a formula that concatenates a value only if it is less than $5,000.

=IF(E3>5000,”NO”,CONCATENATE(D3,”,”,C3))

The IF formula detects the cells with values above $5,000. The formula concatenates the names associated with amounts exceeding $5,000. However, the formula returns “NO” if they do not meet the criteria.

Pairing CONCATENATE with IF is especially helpful if you want to use several criteria to combine multiple values into a single result.

Alternatives to the CONCATENATE Function in Google Sheets

You can use other formulas in Google Sheets to replicate the CONCATENATE function.

JOIN Function

Suppose you want to combine names from multiple cells into one cell, with each name in a new line.

You can accomplish this with CONCATENATE, but you can also do it with the JOIN function as well.

The JOIN function concatenates the elements of single or multiple one-dimensional arrays with a specified delimiter.

The JOIN function’s syntax is:

JOIN(delimiter, value_or_array1, [value_or_array2, …])

• delimiter is the string to be placed between each value.
• value_or_array1 is the first value
• value_or_array2, value_or_array3… are the additional arrays or values you wish to append using the delimiter.

A JOIN formula would look something like this:

=ARRAYFORMULA(JOIN(char(10),C3:C10&” “&D3:D10))

You can type ARRAYFORMULA before JOIN or use the Ctrl+Shift+Enter keys to append the ArrayFormula automatically.

The formula’s result should look like this:

The formula’s char(10) perimeter adds a line break to your results. Learn more in our guide on how to use the ArrayFormula function in Google Sheets.

CONCAT function

A CONCAT formula in Google Sheets works similarly to the CONCATENATE function.

The functions work in exactly the same way. However, the CONCAT function can only combine two text strings, whereas CONCATENATE can merge more cells and ranges.

Overall, the Google Sheets CONCATENATE function allows you to create more complex formulas with larger volumes of data.

Important Considerations When Using CONCATENATE in Google Sheets

Here’s how you can maximize the power of the CONCATENATE function in Google Sheets, and avoid common errors.

• Text separator. CONCATENATE does not automatically insert a space between the inputs. To do this, you must include your preferred separator as one of the inputs in your concatenate formula.

• Number conversion. Inputs that are numbers are converted to text before concatenation. If the number has leading or trailing zeros, they’ll disappear during the process.

• Cell formatting. The CONCATENATE function’s final result will inherit the formatting of the first input.

• Maximum length. The maximum length of the final string produced by the CONCATENATE function is 32767 characters. Concatenated strings exceeding this length are truncated.

• The maximum number of arguments. The CONCATENATE function can handle a maximum of 30 arguments at a time.

• Inconsistent data types. The function won’t return your expected results if the inputted data types are different. You cannot merge text and numbers.

• Performance limitations. Using the CONCATENATE function on a large number of cells can slow down your spreadsheet.

Consider these factors when you use the CONCATENATE function, so you can avoid mistakes, and ensure the function produces your desired results.

CONCATENATE in Google Sheets: Automatically Join Values Without the Hassle

The CONCATENATE function makes it easier to combine values into a single string. It’s a lifesaver at times, especially when you need to join a series of values together and don’t want to deal with copy-paste.

And when it comes to simplifying your spreadsheet workflows, Coefficient makes it effortless and pain-free to import and sync live data from your business systems into Google Sheets.

Try Coefficient for free now to pull data into your spreadsheet instantly and automate your spreadsheet processes.