How to concatenate columns in a query

05 Jul 2018
functions 1 minute read

How to concatenate columns in a query and return the results.


If you want to produce aggregated columns in your query results (like concatenated columns), then you can do this without needing to add extra columns!


By using the arrayformula function in conjunction with concatenate you can supply concatenated columns into your query formulas.

{ARRAYFORMULA(A2:A&", "&B2:B&" ("&C2:C&")"),K2:K}

The formula above creates a two-column list, with the first column being a concatenation of columns A, B and C. This is useful if you have names and a group/identifier in these columns. If column A contains a surname (Doe), column B a given name (John) and column C a form group (9A) then then concatenated column will produce: “Doe, John (9A)”. Column K in this example might be a status column or a value upon which you want to select, e.g. show all the people who are going on a trip.

IFERROR(QUERY({ARRAYFORMULA(A2:A&", "&B2:B&" ("&C2:C&")"),K2:K}, "select Col1 where Col2 = TRUE or Col2 = 'TRUE'", 0),)

This data set can then be fed into a normal query function, to produce a filtered set of results, returning just the concatenated column! The outer iferror function is used to catch the condition where no rows are returned, showing a blank rather than an error.

Tagged Google, Sheet

Related Google Calendars on the Web (01 Sep 2018), Protecting Data with Google Drive (14 Aug 2018), Dealing with confidential information in schools (24 Jul 2018)