1

I'm looking for a single arrayformula to convert a specified range, such as B2:D4, into a list of addresses (not values!) contained in that range, in this case B2,B3,B4,C2,C3,C4,D2,D3,D4.

Attempts so far

Google didn't come up with useful results.

I have tried to count through the array, and are able to extract two list of numbers corresponding to the row and column numbers, but have failed to come up with a combined formula.

Column calc:

=ArrayFormula(
   ROW(INDIRECT(SUBSTITUTE(ADDRESS(ROW(),ROW())&":"&ADDRESS(ROW()+
      COLUMNS(B2:D4)
   -1,ROW()),"$","")))-ROW()+
      CELL("col", B2:D4)
)

Row calc:

=ArrayFormula(
   ROW(INDIRECT(SUBSTITUTE(ADDRESS(ROW(),ROW())&":"&ADDRESS(ROW()+
      ROWS(B2:D4)
   -1,ROW()),"$","")))-ROW()+
      CELL("row", B2:D4)
)

Note on the above formulas, the ROWS()/COLUMNS() function is used to get the range size, and the CELL() is used to get the range start.

2

This one is fairly strait forward, please see the code below:

=textjoin(",",true,arrayformula(address(row(B2:D4),column(B2:D4),4)))

You can change the last 4 to a 1 if you want absolute references.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.