# Extracting range from of an array

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!

• 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 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()`

``````=join(",",
QUERY(
SPLIT(A1,",",true),
"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

``````=SPLIT(A1,",",true)
``````

Then `Offset` + `array_constrain` to pick what you need.

``````=ARRAY_CONSTRAIN(OFFSET(A2:J2,0,A6),1,count(A2:J2)-(A7-A6)-1)
``````

Finally using join() :

``````=JOIN(",",
ARRAY_CONSTRAIN(OFFSET(A2:J2,0,A6),1,count(A2:J2)-(A7-A6)-1)
)
``````

Demo • 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 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 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 at 19:09