0

I have this file with an input table in Google Sheets.

Keys Tags V1 V2
kEp tag1 30 12
PgZ tag2 8 2
pac tag3 15 21

This is what i did; I added REGEXREPLACE(QUERY({A1:D},"Select Col1"),".+"," ") to get the empty column I

=ArrayFormula({
  QUERY({A1:D}," Select Col1,Col2,Col3 ",1),
  REGEXREPLACE(QUERY({A1:D},"Select Col1"),".+"," "),
  QUERY({A1:D}," Select Col1,Col2,Col4 ",1)})

enter image description here

The ask

Is there is a simple way with the same range refrence this case A1:D to add an empty column to the array {} like this &""& ?

Osm
  • 2,699
  • 2
  • 4
  • 26
  • how important is it that it be truly empty? because an (invisible) space would be pretty simple. – MattKing Sep 28 '22 at 13:30
  • To be used multiple times without lambda limitation when the the data get larger, simple like `&" "&` invisible) space would do. if not it need to be retreaved and adjusted from the provided refrence. – Osm Sep 28 '22 at 13:32
  • 1
    [mattking](https://stackoverflow.com/users/11075129/mattking) Answerd in [2022-09-28 13:33:59Z](https://stackoverflow.com/posts/73882373/timeline#history_4b288c10-babc-45fb-90bd-4682436c8b9c) `AND` [themaster](https://stackoverflow.com/users/8404453/themaster) commented in [2022-09-28 13:33:51Z](https://stackoverflow.com/questions/73881199/adding-empty-column-in-an-array-in-google-sheets/73882373?noredirect=1#comment130456589_73882248), @MattKing , @themaster – Osm Sep 28 '22 at 13:44

3 Answers3

3

If 'empty' doesn't really have to be that empty, this is pretty simple...

=QUERY({A1:D4,A1:B4},"select Col1,Col2,Col3,' ',Col5,Col6,Col4 label ' '''")

enter image description here

MattKing
  • 7,373
  • 8
  • 13
1

You can try-

={QUERY({A1:D}," Select Col1,Col2,Col3 where Col1 is not null",1),
  FLATTEN(SPLIT((REPT(" |",COUNTA(A:A))),"|")),
  QUERY({A1:D}," Select Col1,Col2,Col4 where Col1 is not null",1)}

And simplified formula-

={QUERY(A:D,"select A,B,C where A is not null",1),
  FLATTEN(SPLIT((REPT(" |",COUNTA(A:A))),"|")),
  QUERY(A:D,"Select A,B,D where A is not null",1)}
Harun24hr
  • 30,391
  • 4
  • 21
  • 36
1

Get different cuts of the range through OFFSET and join them along with empty arrays crafted with MAKEARRAY:

=LAMBDA(rg,where,how_many,
  {
    OFFSET(rg,0,0,,where),
    MAKEARRAY(ROWS(rg),how_many,LAMBDA(r,c,)),
    OFFSET(rg,0,where,,COLUMNS(rg)-where)
  }
)(A1:INDEX(D:D,COUNTA(D:D)),1,2)
TheMaster
  • 45,448
  • 6
  • 62
  • 85