Table of Contents
The CONCAT and QUERY functions can be used together in Google Sheets to combine the results of multiple columns from a single table or the results of multiple tables into a single column. The CONCAT function can be used to combine the values from multiple columns into a single column, while the QUERY function can be used to filter the results and order them. The CONCAT and QUERY functions can be used together to create powerful queries that return the desired results.
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.