Utilizing QUERY and CONCATENATE in Google Sheets for Advanced Data Insights

Imagine you've got a spreadsheet in Google Sheets with sales figures everywhere. Maybe it's product names in one column and their corresponding prices in another. You want to pair them up as a "Widget - $10" without doing it manually for every item. Well, CONCATENATE and QUERY will definitely help you with this task.

CONCATENATE helps you combine text from different cells, while QUERY can filter through your sales data. When you put them together, you've got a simple way to merge and organize your spreadsheet.

Whether you create reports, analyze information or simply try to keep your spreadsheet tidy, learning these functions will make your work much easier.

In this guide, we'll solve the problem of combining CONCATENATE with QUERY and show you new ways to mix columns using QUERY. In the end, you'll get a template with all the formulas we've shared here, so you can start using them in your work immediately.

CONCATENATE and QUERY serve different purposes: CONCATENATE merges text or data from various cells, while QUERY is great for sorting and filtering data. CONCATENATE doesn't directly work with QUERY's filtering features. So, even if you successfully merge the data using CONCATENATE, the filtered results won't be reflected accurately. This mismatch in how the functions handle data can lead to unwanted outcomes and frustration for users.

Yet, with some trial and error, you can solve these problems.

To add to your toolkit, check out our guide on the UNIQUE function in Google Sheets. It will help you clean up your data before merging or filtering it with CONCATENATE and QUERY.

We're going to work with the dataset provided below to show you 2 simple methods of using the QUERY function to merge 2 columns in Google Sheets. This dataset includes Product IDs and Product Names from a store.

This method uses the TRANSPOSE and QUERY functions in Google Sheets to concatenate values from multiple rows into a single cell.

Syntax:

Here is the breakdown:

• TRANSPOSE(): This function transposes the vertical output of the QUERY function into a horizontal format, resulting in a single row of concatenated values.
• QUERY(range, "select column1 & 'separator' & column2", 0):
  • range: the range of cells containing the data you want to concatenate. It could be a range of 2 or more columns.
  • "select column1 & 'separator' & column2": This is the query string that specifies how the data should be concatenated. Replace column1 and column2 with the actual column references within the range. The & operator concatenates the values of column1 and column2, and you can replace 'separator' with any desired separator (e.g., hyphen, comma, space) to separate the concatenated values.
  • 0: This parameter specifies that there are no headers in the selected range. If your range has headers, you would use 1 instead of 0.

Example:

We have a list of products, where each product has a name and a code associated with it. To merge each product's name with its corresponding code into a single entry, let's use the following formula:

After applying it, we end up with a single column where each cell contains a product's name followed by its code, making the data more manageable and easier to work with.

Sometimes in Google Sheets, you might want to join together the results of a search. You can do this using a clever mix of functions like ARRAYFORMULA, SUBSTITUTE, TRIM, TRANSPOSE, QUERY, and COLUMNS.

Using a similar list of products with names and codes, let's combine the data from Column B and Column C, but only for rows where the Product ID in Column C is 101. To achieve this result, use the following formula:

Let's break down the formula:

• QUERY(B3:C12, "select * where C contains '101'"): This part selects all rows from columns B and C where the value in column C contains '101'. So, it filters the rows based on the condition that the Product ID (in column C) contains '101'.
• TRANSPOSE: Transposes the resulting array, making rows into columns and vice versa.
• QUERY(TRANSPOSE(...),,COLUMNS(QUERY(B3:C12, "select * where C contains '101'"))): This part applies another query to the transposed array to concatenate the values row-wise.
• TRANSPOSE: Transposes the result again to bring it back to the original orientation.
• TRIM: This function trims any extra spaces.
• SUBSTITUTE(...," ","_"): Replaces spaces with underscores.
• ARRAYFORMULA: Allows the formula to process arrays of data all at once, rather than having to input the formula separately for each row.

If you need to simplify your data tasks, we suggest reading our article on using VLOOKUP with IF statements.

In some cases, you may need alternative methods for data concatenation in Google Sheets because of specific requirements, preferences, or limitations. For instance, while the QUERY function is great for filtering and manipulating data, it's not the best for simple concatenation tasks. Alternative methods come with better solutions or address specific needs such as conditional concatenation or optimizing performance.

Pair ARRAYFORMULA and the ampersand (&) operator if you want to merge data (in our example Product IDs and Product names) to create unique labels for your products.

Formula:

ARRAYFORMULA helps combine data for all products in the list, making sure each product has the right combination of details.

Formula that integrates IF and CONCATENATE is useful for creating specific combinations. In this case, it checks if the Product ID in column C is equal to 101. If it is, it combines the Product name and ID with an underscore between them. If not, it leaves the cell blank, helping you filter out products that don't meet this condition.

Formula:

When dragging this formula down, you will automatically update the cell references to C4, C5, and so on, for the Product ID column, and B4, B5, and so on, for the Product name column.

If you're looking for a method with fewer variables, the simplified CONCATENATE function is what you need. It directly combines values from specified cells, making it an easier solution for merging data without any extra steps.

Function:

Combining ARRAYFORMULA with the CONCAT function in Google Sheets offers a simple way to merge data.

ARRAYFORMULA lets CONCAT work across several cells at once, so you don't have to manually apply the formula to each row. It's a big time-saver, especially when you're dealing with lots of data.

Function:

When merging QUERY with other functions in Google Sheets, there are a few potential problems to be aware of. Let's take a look at them and find solutions on how to avoid these issues:

⚠️ Issue: The problem occurs when you try to directly use CONCATENATE within a QUERY function or vice versa. This doesn't work as it should because the QUERY function doesn't naturally support text concatenation as part of its query language.

✅ Solution: Use ARRAYFORMULA with JOIN or TEXTJOIN. This combo lets you merge text directly from a QUERY operation.

⚠️ Issue: QUERY can return multiple rows and columns as an array, making direct concatenation problematic. Users may experience errors when they try to merge these array outputs without using specialized array-handling functions, like ARRAYFORMULA.

✅ Solution: If your version of Google Sheets supports it, use the FLATTEN function, which converts multi-row and multi-column arrays into a single column, making it easier to manage QUERY results when combining them with other functions.

⚠️ Issue: Building a formula that combines QUERY with other functions demands a good understanding of how Google Sheets works with arrays and text. Errors often come from syntax mistakes like improper use of quotation marks or parentheses, or misunderstanding the sequence of operations.

✅ Solution: Simplify complex formulas by breaking them down into smaller ones and employ named ranges using Data > Named ranges in Google Sheets. This approach makes the overall formula easier to read, debug, and manage.

⚠️ Issue: Working with large datasets can slow down your spreadsheet when using QUERY and text manipulation functions – ARRAYFORMULA with SUBSTITUTE or TRIM. This delay can make your calculations sluggish and the sheet less responsive.

✅ Solution: To speed things up, select only the columns you need and apply filters within the QUERY function. This reduces the amount of data and improves performance, especially with big datasets.

⚠️ Issue: Mistakes in selecting the right columns or conditions in the QUERY function can lead to pulling the wrong data for concatenation, especially with complex conditions or multiple columns.

✅ Solution: To handle this, consider prepping your data with FILTER or ARRAYFORMULA functions before using QUERY, especially for more complex filtering needs.

⚠️ Issue: After you import data, it may need formatting or cleanup, like removing extra spaces or standardizing text cases. Neglecting these steps before merging can lead to concatenated strings that don't meet your criteria or format.

✅ Solution: Clean up your data using TRIM, CLEAN, and SUBSTITUTE before concatenation. These functions remove extra spaces, and non-printable characters, and replace specific characters, ensuring your data is in the right format for concatenation and analysis.

⚠️ Issue: Sometimes, when putting together query results, you need to add custom separators between values. Doing this with CONCATENATE or the & operator can be tricky and add complexity.

✅ Solution: Try using TEXTJOIN instead. It lets you set custom delimiters and works with QUERY and ARRAYFORMULA to join strings with your chosen separator. It's a simpler way to format your concatenated data just the way you want it.

Mastering QUERY and CONCATENATE functions is invaluable for any professional working with data. 

Google Sheets boasts an impressive collection of formulas that greatly enhance your data handling and insight generation capabilities:

• VLOOKUP: Essential for pinpointing precise information within a table, this function makes data retrieval efficient and straightforward.
• XLOOKUP: Building on VLOOKUP's foundation, XLOOKUP offers expanded flexibility and a modernized method for searching and retrieving data.
• ARRAY: Skilled in executing simultaneous calculations on array data, this formula outputs results in an array, facilitating in-depth analysis.
• UNIQUE: Efficiently scans through a data set to remove duplicates, ensuring that only unique entries are displayed.
• Pivot Table: A dynamic tool that streamlines the process of summarizing, organizing, and analyzing large data sets to easily extract insights and detect trends.
• IMPORT Functions: Indispensable for importing data - Like with Query and ImportRange from a variety of external sources, including websites, other Google Sheets, or RSS feeds, into your spreadsheet, thereby enhancing your data analysis and integration efforts.

You can also look check this latest article on how to combine Query and ImportRange.

What if you could take data analysis to the next level without spending hours manually crunching numbers in Google Sheets? And what if there was a way to automate the process, allowing you to focus on insights rather than repetitive tasks?

We have good news - there is a tool to do all that!

With OWOX BI BigQuery Reports Extension, you can improve the analysis workflow and stop working manually with data in Google Sheets. OWOX BI Add-on will prepare your data, automate processes, and bring you ready-to-use figures for further analysis. Therefore, you will integrate the results with the functions we've discussed in this article, making your work faster, easier, and more insightful than ever before.