All resources
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.
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.
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.
QUERY(data, query, [headers])
When you put them together, you’ve got a simple way to combine cells, merge cells, and organize your spreadsheet.
QUERY and CONCATENATE functions serve different purposes:
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.
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.
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:
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.
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:
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 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.
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.
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.
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)
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))
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:
⚠️ 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.
⚠️ 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 the 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. 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.
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:
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!
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.
