From the following single cell array:

0, 1, 1, 2, 3, 5, 8, 13, 21, 34

I'd like to extract 3,5,8,13,21 into a single cell by specifying index 4 : index 8 of the split of that cell.

The Challenge is that I'll need it be in a single step (cannot split array into different cells, as these arrays will get very long and will otherwise produce far too many cells for google sheets to handle.

I appreciate the help!


2 Answers 2


To get the desired result from your example in just one formula please try the following:

=REGEXREPLACE(JOIN(", ",TRANSPOSE(QUERY(TRANSPOSE(SPLIT(A1,",",true)),"select * offset "&8-4&" "))),"(, \d+$)","")

(assuming A1 holds your numbers)

I hope this is what you are looking for. If not let us know.


Update :

Here's a way to do it with =query()

        "SELECT "&JOIN(",",ARRAYFORMULA("Col"&ROW(indirect("A"&A6+1&":A"&A7+1))))

With :

  • A1 as the initial cell
  • A6 is parameter 1
  • A7 is parameter 2

The demo sheet has been updated.

Using a single step :

First split() to create an array


Then Offset + array_constrain to pick what you need.


Finally using join() :



enter image description here

  • Ah thanks pjmg! Though, is there any way to do this in a single step without splitting the cell up into a multi-cell array?
    – devans
    Feb 19, 2020 at 14:34
  • I'm not sure, I tried with index() (spoiler : doesn't do the job). There's maybe a way with =query() but it can be very hard to scale this way if you need to re-use the formula IMO multiple times or on another sheet. I'll edit my answer later if I find a way.
    – pjmg
    Feb 19, 2020 at 18:14
  • @devans I updated my answer and the demo sheet. Don't forget to accept the answer if it correspond to what you're looking for
    – pjmg
    Feb 19, 2020 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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