Combining ARRAYFORMULA with functions whose arguments can be rectangular ranges

Question

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 1   ad      fg      re      adfgre
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?

Answer 1

=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.

Answer 2

=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

Answer 3

• simmilar approach and a bit shorter:

=ARRAYFORMULA(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE(A3:E11), , 50000)), " ", ""))

Answer 4

=ARRAYFORMULA(CONCAT(CONCAT(A1:A, B1:B), C1:C))

Answer 5

• 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("")]
  })
  }