How to Use CONCATENATE and QUERY for Advanced Data Insights in Google Sheets
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, and you want to merge these columns in Google Sheets.
You want to pair them up as a “Widget - $10” without doing it manually for every item. Well, the CONCATENATE function and QUERY function will help you with this task.
Whether you create reports, analyze data, or organize your spreadsheet, learning these functions will simplify your tasks.
This guide will show how to use the CONCATENATE function with the QUERY function and explore new ways to mix columns using QUERY. You'll also get a template with all the formulas shared, ready for immediate use.
Overview of CONCATENATE and QUERY Functions in Google Sheets
The CONCATENATE function is useful if you want to combine cells. Suppose you have two names in different columns, and you want to combine them. You can use the CONCATENATE function to combine the cells and get the desired result.
Syntax to CONCATENATE Cells:
CONCATENATE(string1, [string2, ...])
On the other hand, QUERY can filter through your sales data. Suppose you have a sales dataset with products and sales amounts in different columns and want to filter only sales greater than $500. You can filter the data and get relevant results from the QUERY function.
Syntax of QUERY:
QUERY(data, query, [headers])
When you put them together, you’ve got a simple way to combine cells, merge cells, and organize your spreadsheet.
The Challenge of Directly Merging CONCATENATE in Google Sheets with QUERY
QUERY and CONCATENATE functions serve different purposes:
- The CONCATENATE merges text or data from various cells.
- The QUERY is great for sorting and filtering data.
When you concatenate cells using CONCATENATE, it doesn’t directly work with QUERY’s filtering features.
So, even if you use the CONCATENATE function to successfully merge the data using the filtered results won’t be reflected accurately. This mismatch in how the functions handle data can lead to unwanted outcomes and user frustration.
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 the CONCATENATE function and QUERY function.
Innovative Approaches to Concatenating Columns with QUERY
We’re going to work with the dataset provided below to show you two simple methods of using the QUERY function to merge columns in Google Sheets. This dataset includes Product IDs and Product Names from a store.
Method #1: Integrating TRANSPOSE with QUERY Functions for Concatenation
This method uses the TRANSPOSE and QUERY functions in Google Sheets to concatenate values from multiple rows or entire columns into a single cell.
Syntax:
=TRANSPOSE(QUERY(range, “select column1 & ‘separator’ & column2”, 0))
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 query string specifies how the data should be concatenated. Replace column 1 and column 2 with the actual column references within the range. The & operator concatenates the values of column 1 and column 2, 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, each with 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:
=TRANSPOSE(QUERY(TRANSPOSE(B3:C12),,6^6))
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.
Method #2: Concatenating with ARRAYFORMULA, SUBSTITUTE, TRIM, TRANSPOSE, QUERY, and COLUMNS Functions
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 to combine data from two 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:
=ARRAYFORMULA(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(QUERY(B3:C12, “select * where C contains ‘101’”)),,COLUMNS(QUERY(B3:C12, “select * where C contains ‘101’”))))),” “,”_”))
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 function to CONCATENATE function the values row-wise.
- TRANSPOSE: Transposes the result to return 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.
4 Alternative Techniques for Data Concatenation Using QUERY
In some cases, you may need alternative methods for data concatenation in Google Sheets due to specific requirements, preferences, or limitations, such as combining multiple strings or merging columns in Google Sheets. For instance, while the QUERY function is excellent for filtering and manipulating data, it’s not always the most efficient for simple concatenation tasks.
Alternative methods often provide better solutions or address particular needs, such as conditional concatenation or optimizing performance. These methods allow you to achieve more flexibility and efficiency, depending on your specific use case in Google Sheets.
Technique #1: Pairing ARRAYFORMULA and Ampersand for Data Combination
Pair ARRAYFORMULA and the ampersand (&) operator if you want to join cells and merge data (in our example - Product IDs and Product names) to create unique labels for your products.
Formula:
=ARRAYFORMULA(B3:B12&”_”&C3:C12)
ARRAYFORMULA helps combine data for all products in the list, making sure each product has the right combination of details.
Technique #2: Use the CONCATENATE Function with IF Google Sheets for Enhanced Merging
A formula that integrates IF and CONCATENATE in Google Sheets is useful for creating specific combinations and can be particularly effective when merging cells. In this case, it checks if the Product ID in column C equals 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:
=IF(C3=101, CONCATENATE(C3, “_”, B3), “”)
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.
Technique #3: Simplifying with Google Sheets CONCATENATE Function
Use the CONCATENATE function if you’re looking for a method with fewer variables. It directly combines values from specified cells, making it easier to merge data without any extra steps in Google Sheets.
Formula:
=CONCATENATE(B3," ",C3)
Technique #4: Combining ARRAYFORMULA with CONCAT for Efficiency
Combining ARRAYFORMULA with the CONCAT function in Google Sheets offers a simple way to merge data, similar to how you would use the CONCATENATE function in sheets to merge text strings and data from multiple cells into one cell.
ARRAYFORMULA lets CONCAT work across several cells simultaneously, so you don’t have to apply the formula to each row manually. It’s a big time-saver, especially when you’re dealing with lots of data and need to concatenate cells efficiently in Google Sheets.
Formula:
=ARRAYFORMULA(CONCAT(B3:B12,C3:C12))
Common Pitfalls in Merging QUERY with Other Functions (and How to Avoid Them)
Different formulas or methods can yield the same result when merging the QUERY with other functions in Google Sheets, but 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:
Pitfall #1: Direct-Combination Limitation
⚠️ Issue: The problem occurs when you try to directly use the CONCATENATE function 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.
Pitfall #2: Handling Array Outputs from Complex QUERY Combinations
⚠️ 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.
Pitfall #3: Complexities of Intricate Formula Structures
⚠️ 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.
Pitfall #4: Performance Issues with QUERY on Large Datasets
⚠️ 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.
Pitfall #5: Column Specification Errors in QUERY Function
⚠️ 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 the QUERY, especially for more complex filtering needs.
Pitfall #6: Formatting Inconsistencies Before Concatenation
⚠️ 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.
Pitfall #7: Separator Integration Challenge in Concatenated Queries
⚠️ Issue: Sometimes, when putting together query results, you need to add custom separators between values. Use the CONCATENATE function 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.
Automating Large-Scale Data Analysis
Mastering the QUERY and CONCATENATE functions is invaluable for any professional working with data, especially when merging multiple cells into one cell in Google Sheets.
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.
- 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.
- FILTER Function: Extract and display only the data you need from a range.
- SEARCH Function: Locate specific text within a string for easy data extraction.
- GOOGLEFINANCE Function: Retrieve real-time financial data directly into your spreadsheets.
You can also check this latest article on how to combine QUERY and IMPORTRANGE.
What if you could elevate your data analysis without hours of manual work in Google Sheets? And what if you could automate the process, freeing you to focus on insights instead of repetitive tasks?
Good news – there’s a tool for that!
Enhance Your Data Insights with the OWOX: Reports, Charts & Pivots Extension
With OWOX Reports, Charts & Pivots Extension, you can improve the analysis workflow and eliminate the need to work manually with data in Google Sheets. The OWOX Reports Extension for Google Sheets prepares your data, automates processes, and provides ready-to-use figures for further analysis.
By integrating the results with the functions in this article, you can work faster and easier and gain deeper insights. This combination boosts both efficiency and data analysis.
FAQ
