# 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!

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+\$)","")

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? 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