# Combining ARRAYFORMULA with functions whose arguments can be rectangular ranges

I mean to concatenate several columns, so use of `CONCATENATE` is preferred over `&` (here I post an example with only 3 columns). For instance, D1 would contain `=CONCATENATE(A1:C1)`. Then I would copy the formula down to D20 (e.g.)

``````        col A   col B   col C   col D
row 2   as      we      sd      aswesd
...
``````

Since the argument of `CONCATENATE` can be any (sequence of) rectangular range, it seems I cannot use in D1 `=ARRAYFORMULA(CONCATENATE(A1:C20))` for the same purpose, since it understands I mean to concatenate the contents of all cells in that range, and place the result in a single cell (D1). This produces the same result as using `=CONCATENATE(A1:C20)` in D1, so `ARRAYFORMULA` has no effect here.

It seems that (roughly speaking) when combining functions (e.g., `CONCATENATE`) whose arguments can be rectangular ranges (and actually passing rectangular range arguments) with `ARRAYFORMULA`, there is an ambiguity in what one wants. So, if using `ARRAYFORMULA(CONCATENATE(range))`, one may want to use `range` as an argument for a single calculation, in which case the output of `ARRAYFORMULA` is a single cell, or one may want to use `range` as a set of "subranges" for many calculations, in which case the output of `ARRAYFORMULA` is a similar set of cells. As per what I found, this ambiguity is resolved in favor of the first case.

How can I use `ARRAYFORMULA` to instruct a function whose arguments can be rectangular ranges to actually produce a result in multiple cells, instead of applying the argument to produce a result in a single cell?

``````=ARRAYFORMULA(REGEXREPLACE(TRANSPOSE(SPLIT(
TEXTJOIN("♂", 1, {Q1:T, U1:U&"♀"}), "♀")), "♂", ""))`````` • Q1:T is the full range minus last column
• U1:U is the last column of full range
• eg. this will concentrate each row from range Q1:U into one cell as an array
• The objective is stated in the question at the end of the OP. I do not mean to attain the specific result of my example, I know how to do that. I do not mean to get an alternative formula for the example that involves `ARRAYFORMULA`. I mean to answer the generic question, which is illustrated and inspired by the example. – sancho.s Feb 22 at 15:09
• I read your question several times and yet, I still fail to understand the purpose of it. as I understood, your end goal is to find a formula which can take a "multi-row-multi-column" range and execute joining column's content into a single column in such way that every row from the given range will be concentrated on respective row. and yet again, from some untold reason you are against using `CONCAT` nesting as well as formula from this answer regardless of the fact that it would bring you desired output you stated yourself. – user0 Feb 24 at 12:32
• there just isn't a regular formula which could do what you need and look like: `=UNKNOWN(A1:C20)` you would need either custom formula based on script or combination of multiple regular formulas to achieve your goal. I may have 1-2 more ideas how to solve this if you are interested, but I guess you wont be satisfied with either solution... – user0 Feb 24 at 12:35

``````=ARRAYFORMULA(REGEXREPLACE(
TRANSPOSE(QUERY(TRANSPOSE({A3:F10;"","","","","",0}),"where Col1!=0"))," ",""))`````` • this solution exploits the broken logic of `QUERY`. it can take a rectangular range as an argument and doesn't lean on `&` nor other joining methods which are limited to a maximum of 50000 characters. the only "disadvantage" would be that if you want to join range `A:K` you will need to input range `A:L` - eg. your desired range +1 column.
• simmilar approach and a bit shorter:

``=ARRAYFORMULA(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE(A3:E11), , 50000)), " ", ""))`` ``=ARRAYFORMULA(CONCAT(CONCAT(A1:A, B1:B), C1:C))`` • `CONCAT` is equivalent to `&`. It is not "a function whose arguments can be rectangular ranges". How would you say this addresses the question? – sancho.s Feb 22 at 11:44
• My end goal is stated in the boldfaced question... CONCATENATE is an example that I came across, but the question applies to many other functions for which rectangular ranges are acceptable arguments. In those cases, there would be an ambiguity in what one wants when combined with ARRAYFORMULA. – sancho.s Feb 22 at 14:59
• custom function (script) / formula

``=CC(A3:E11)``

``````function CC(range) {
return range.map(function (r) {
return [r.sort().join("")]
})
}``````

• untested:
``````function CC(range) {
return range.map(function (r) {
return [r.join("")]
})
}``````
• This does not use ARRAYFORMULA as the OP means. – sancho.s Feb 26 at 20:23