This new post/tutorial is actually not about how to use the function Query to combine columns in Google Sheets. Of course, it’s part of the tutorial. The main topic here is how to insert or to add separators to the Query combined values in columns.
We can use the QUERY function to concatenate columns in Google Sheets. But it’s not a must to use this function for combining columns as we can use the & operator with ArrayFormula for the same.
Ampersand to Combine Columns B to E (Formula # 1):
=ArrayFormula(
B1:B&" "&C1:C&" "&D1:D&" "&E1:E
)For the said purpose, the Query has one advantage over the ampersand. What’s that?
If you have several columns, using ampersand is not recommended as you require to specify columns individually as above.
When using Query to combine columns, we can specify columns as a range. It’s a big advantage. In addition to this, the ampersand is not reliable if the number of records in the range is spread across several rows. It may lead to some kind of ‘limitation’ error!
The above-mentioned Query usage has already been featured in one of my Google Sheets tutorials titled The Flexible Array Formula to Join Columns in Google Sheets.
As mentioned at the beginning, here my main focus is not on how to combine columns using Query but on how to insert separators aka delimiters in such combined columns.
But without an example of how to use the Query function to combine columns, we can’t go further. So let me start with that example first.
Using Query Header Clause to Combine Columns
Here using Query Header (the last clause as per the clause order in Query), I’m going to combine or concatenate multiple columns in Google Sheets.
The below Query in cell G1 combines columns B, C, D, and E and inserts a white space as the separator. The white space is not required to specify in the formula though.
Query to Combine Columns B to E (Formula # 2):
=transpose(
query(
transpose(B:E),,9^9
)
)
Formula Explanation:
Query Syntax: QUERY(data, query, [headers])
data – As per the Query function syntax, transpose(B:E) is the ‘data’ argument in the formula.
Why should one use Transpose with the range (data) in Query here?
The Transpose function converts the values in rows to columns. Actually, Query combines values in rows. If we do not use Transpose, then the combined output would be as below.
Score 1 45 40 45 40 30 Score 2 25 50 26 49 50 Score 3 39 39 38 37 39 Score 4 47 39 45 query – The ‘query’ argument is left blank.
headers – In ‘headers’ we can use any large number so that I have used the number 9^9 which is equal to # 387420489. It makes the Query function to ‘think’ that all the records in the ‘data’ are headers.
Two Ways to Insert Separators in Query Combined Columns
In such Query combined columns in Google Sheets, we can follow two methods to insert separators such as pipes, caret, forward slash, hash, tide, coma, etc. One using the ampersand operator and the other using the Regexreplace function.
I prefer the Regexreplace method as it’s the best way to insert separators/delimiters when using Query to combine cells in Google Sheets. Here are those two methods.
Adding Separators Using Ampersand and Subsequent Issues
Here I am following a step-by-step approach.
As per this method, first of all, concatenate the required separator with the range. Since B:E is the range, the reference in the formula # 2, i.e. B:E, should be used as below.
B:E&" |")I have used the Pipe delimiter. Since Query has already added a space between columns as the separator, you should only add one space before the pipe as above and no space after it.
The output would be a column with the values concatenated from multiple columns as below.
Now we have two issues to sort out in hand. What are they?
- As you can see, there are 4 delimiters/separators in each row because the number of columns to combine in the range is 4. The formula inserts delimiters in blank rows too! We want to skip blank rows.
- The second issue is the extra delimiter at the end of all the values in all the rows.
The first problem we can solve by filtering the range using the Filter formula as below.
filter(B:E,B:B<>"")So the Query formula to combine the columns in B:E would be as below.
=ArrayFormula(
transpose(
query(
transpose(filter(B:E,B:B<>"")&" |"),,9^9
)
)
)The second problem, which is the extra delimiter/separator at the end. We can use the Regexreplace function to eliminate that.
I have a detailed post on this topic (Regexreplace use) here – Remove Repeated Characters from the End of Strings in Google Sheets.
So the formula would be;
Query Formula to Combine Columns and to Add Separators (Formula # 3):
=ArrayFormula(
regexreplace(
transpose(
trim(
query(
transpose(filter(B:E,B:B<>"")&" |"),,9^9
)
)
)&" ",
"(\|\s){1,}$",""
)
)
Now to the next method.
Separators Using Regexreplace and Its Pros and Cons
Here in this approach also you must filter out the blank rows from the range (data). Then Trim the result to remove unwanted/extra space characters that Query places.
=ArrayFormula(
TRIM(
transpose(
query(
transpose(filter(B:E,B:B<>"")),,9^9
)
)
)
)Then using Regexreplace, we can replace the space characters between values with the pipe or the separator that we want. Here I am using the pipe as the separator.
Formula # 4:
=ArrayFormula(
regexreplace(
trim(
transpose(
query(
transpose(filter(B:E,B:B<>"")),,9^9
)
)
),"\s"," | "
)
)When you combine columns using the Query Header clause as above there are some pros and cons.
Pros:
- In the first method (formula # 3), we have used
&to insert separators. That operator is prone to return an error in a very large set of data. Since we have not used it here (formula # 4), we are somewhat safe.
Cons:
- The Regexreplace replaces space characters with the required separator, right? If the value in a cell itself contains space, then the output won’t be correct.
I hope you could learn how to use Query to combine columns and to add delimiters properly in Google Sheets. Thanks for the stay. Enjoy!
