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!

  • 1
    Have you tried the solutions given to you? Did any work for you? If not we should try finding out why. If yes, as per site guidelines when an answer addresses your question, you should accept it and maybe upvote it so others can benefit as well. – marikamitsos Feb 20 '20 at 19:05

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 '20 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 '20 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 '20 at 19:09

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.