Google Sheets: How to Use CONCAT with QUERY


Often you may want to concatenate the results of a query in Google Sheets into one cell per row.

Unfortunately it’s not possible to use the CONCAT function with the QUERY function, but you can use the following formula to replicate this functionality:

=ARRAYFORMULA(
SUBSTITUTE(
trim(transpose(query(transpose(your_query),,COLUMNS(your_query))))," ","_")
)

This particular formula concatenates the results of the query using _ as the separator but you can change this separator to be anything you’d like.

The following example shows how to use this formula in practice.

Example: Use CONCAT with QUERY in Google Sheets

Suppose we have the following dataset in Google Sheets:

We can use the following formula to query for rows where the first name contains “Andy” and concatenate the first, middle, and last names into one cell per row:

=ARRAYFORMULA(
SUBSTITUTE(
trim(transpose(query(transpose(query(A:C, "select * where A contains 'Andy'")),,COLUMNS(query(A:C, "select * where A contains 'Andy'")))))," ","_")
)

The following screenshot shows how to use this formula in practice:

Notice that the query only returns the rows where the first name contains ‘Andy’ and the first, middle, and last name are all concatenated into one cell per row using an underscore _ as the separator.

To use a different separator, simply change the “_” at the end of the formula with something else.

For example, the following screenshot shows how to use a space as a separator instead:

Once again the query only returns the rows where the first name contains ‘Andy’ and the first, middle, and last name are all concatenated into one cell per row using a space as the separator.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

Google Sheets Query: How to Use “Not Equal” in Query
Google Sheets Query: How to Return Only Unique Rows
Google Sheets Query: How to Use Multiple Criteria in Query

Leave a Reply

Your email address will not be published.