-1

he exact opposite of what I am looking for. Also, I don't wish to specify individual cell after cell because I think though it can work using & then dragging, it will be a bit messy. Is there a way I can tweak my formula below to perform this task? Thanking you.

=ARRAYFORMULA(IF(D:D="","",CONCATENATE(I1:AM1)))

Anne Vandu
  • 103
  • 10
  • But your formula is in `AM1`? Still you want to concatenate that column too? Or is this a type? – JvdV Jul 15 '20 at 15:38

2 Answers2

3

there is a trick you can use that takes advantage of the "header" parameter in the query function. It adds spaces between each column however, so it might not work for you. If this doesn't work, maybe share a sample sheet so I can show you how it would work for your particular case.

=ARRAYFORMULA(IF(D:D="","",TRANSPOSE(TRIM(QUERY(TRANSPOSE(I:AM),,99)))))
MattKing
  • 7,373
  • 8
  • 13
  • There is, but it's difficult to figure out without you sharing a sample sheet to work from. – MattKing Jul 15 '20 at 16:05
  • I think you can meet your needs taking the formula provided by Matt a step further: `=ARRAYFORMULA(IF(D:D="","",A:A&TRANSPOSE(TRIM(QUERY(TRANSPOSE(I:AM),,99)))))` – marikamitsos Jul 15 '20 at 18:57
  • Sorry. Tried it in your sheet and made a mistake. In your actual case change `A:A` to `I:I` (which is your first column) and then to `J:AM` – marikamitsos Jul 15 '20 at 19:18
  • 1
    hello @Maryongubo I have placed a new formula in cell H1 on a new tab called Matt:King Help that looks like this: =ARRAYFORMULA(IF(D:D="","",TRANSPOSE(SUBSTITUTE(TRIM(QUERY(TRANSPOSE(A:G&CHAR(10)),,99)),CHAR(10)&" ","")))) – MattKing Jul 15 '20 at 19:29
1

try:

=ARRAYFORMULA(IF(D:D="",,SUBSTITUTE(SUBSTITUTE(TRANSPOSE(TRIM(
 QUERY(TRANSPOSE(SUBSTITUTE(I:AM, " ", "♦")),,99^99))), " ", ), "♦", " "))
player0
  • 124,011
  • 12
  • 67
  • 124