Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygoogle-query-language

google sheets, Make letters separated by comma given number of following columns. Not using app script, just formulas please

I'm using the query function in google sheets... I want to select columns F to AB.

I need a cell that has the output F,G,H,I... ,AB so I can put it in the Select statement.

This formula should have 2 inputs, the starting letter (F in this case) and number of following columns (22 in this case)

No app script code please. I know someone can do it with just formulas in one cell.

Solution

  • I believe your goal as follows.

    • You want to create the value of F,G,H,I... ,AB by giving the start column letter of "F" and the number of columns of 22.
      • For example, when the start column letter of "F" and the number of columns of 22 are given, you want to retrieve the value of F,G,H,I... ,AB and you want to use this like =QUERY(A1:AB,"SELECT F,G,H,I... ,AB").
    • You want to achieve this using the built-in functions of Google Spreadsheet without the Google Apps Script.

    For this, how about this answer?

    Sample formula 1:

    In this sample formula, in order to create the value of F,G,H,I... ,AB by giving the start column letter of "F" and the number of columns of 22, I would like to propose the following formula.

    =TEXTJOIN(",",TRUE,ARRAYFORMULA(REGEXREPLACE(ADDRESS(1,COLUMN(INDIRECT(A1&"1:"&ADDRESS(1,B1+COLUMN(INDIRECT(A1&"1")),4))),4),"\d+","")))
    • When A1 and B1 have the values of F and 22, respectively, the flow of this formula is as follows.
      1. Using COLUMN, retrieve the column numbers.
      2. Using ADDRESS, retrieve the column letters from the column numbers. At that time, the row number os removed using REGEXREPLACE.
      3. Above formulas are used with ARRAYFORMULA.
      4. Using TEXTJOIN, join the column letters with ,.
    Result:

    enter image description here

    Sample formula 2:

    In this sample formula, =QUERY(A1:AB,"SELECT F,G,H,I... ,AB") is created using above sample formula 1.

    =QUERY(A1:AB,"SELECT "&TEXTJOIN(",",TRUE,ARRAYFORMULA(REGEXREPLACE(ADDRESS(1, COLUMN(INDIRECT(A1&"1:"&ADDRESS(1, B1 + COLUMN(INDIRECT(A1&"1")), 4))), 4),"\d+",""))))
    Result:

    In this sample result, the range of A2:AB7 is used for QUERY. So please be careful this. When the cells "A1" and "B1" are changed, the result of this formula is also changed.

    enter image description here

    Note:

    • In this case, when the start column and the number of columns are more than the existing maximum columns, an error occurs, please be careful this.

    References:

    Added:

    When you want to create Col6, Col7, Col8 ... Col28 by giving the start column letter of "F" and the number of columns of 22, how about the following sample formula?

    Sample formula:

    ="Col"&TEXTJOIN(",Col",TRUE,ARRAYFORMULA(COLUMN(INDIRECT(A1&"1:"&ADDRESS(1,B1+COLUMN(INDIRECT(A1&"1")),4)))))
    • In this case, please put F and 22 to the cells "A1" and "B1", respectively.