Google-sheets – Extracting range from of an array

google sheetsgoogle-sheets-arrayformulagoogle-sheets-query

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!

Best Answer

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.

Short answer

Instead of SEARCH use REGEXMATCH and JOIN.

Explanation

SEARCH is for searching partial strings into other strings and if it will be used with arrays, both parameters should be arrays of the same size.

JOIN could be used to create a regular expression to be used by REGEXMATCH which in turn will return a array of TRUE/FALSE if the first parameter is an array and it is nested inside a ARRAYFORMULA.

Considering the OP examples, the formula to be used is

=ArrayFormula(REGEXMATCH(E1:E4, JOIN("|",{"Tim","Frank"})))

Google-sheets – Split rows dynamically based on row length (COUNTA) and transpose to 2D array

Solved by Lance over on Google forums.

=ARRAYFORMULA(RegexReplace({
    Transpose(Split(TextJoin("@";1;IF(RegexMatch(IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);"^##1"); IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);));"@";1;1))\

    Transpose(Split(TextJoin("@";1;IF(RegexMatch(IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);"^##2"); IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);));"@";1;1))\

    Transpose(Split(TextJoin("@";1;IF(RegexMatch(IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);"^##3"); IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);));"@";1;1))\

    Transpose(Split(TextJoin("@";1;IF(RegexMatch(IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);"^##4"); IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);));"@";1;1))

};"##\d\s";))

Best Answer

Related Solutions

Google-sheets – How to check if a cell contains any of an array of values

Short answer

Explanation

Google-sheets – Split rows dynamically based on row length (COUNTA) and transpose to 2D array

Related Topic