Incremental autofill in googlesheet-CodePudding

I want to make a table with every 3rd row from the datasource.

1st cell: =TRIM(RIGHT(SUBSTITUTE($B1," ",REPT(" ",20)),20))
2nd cell: =TRIM(RIGHT(SUBSTITUTE($B4," ",REPT(" ",20)),20))
3rd cell: =TRIM(RIGHT(SUBSTITUTE($B7," ",REPT(" ",20)),20))
...

I want cells to be auto filled with this pattern {B1,B4,B7,B10,B13..1 3x}

But when I select first three cells and try autofill, the spreadsheet does this

1st cell: =TRIM(RIGHT(SUBSTITUTE($B1," ",REPT(" ",20)),20))
2nd cell: =TRIM(RIGHT(SUBSTITUTE($B4," ",REPT(" ",20)),20))
3rd cell: =TRIM(RIGHT(SUBSTITUTE($B3," ",REPT(" ",20)),20))
4th cell: =TRIM(RIGHT(SUBSTITUTE($B4," ",REPT(" ",20)),20))
5th cell: =TRIM(RIGHT(SUBSTITUTE($B7," ",REPT(" ",20)),20))
6th cell: =TRIM(RIGHT(SUBSTITUTE($B6," ",REPT(" ",20)),20))
...

How do I achieve this?

CodePudding user response：

Clear a full column (say, Col C, for the sake of explanations here). Then place the following formula in C1 (or in C2 if you want to place a header in C1):

=ArrayFormula(QUERY({IFERROR(REGEXEXTRACT(B:B,"\S $"))},"WHERE Col1 Is Not Null SKIPPING 3"))

This one formula should produce all results in one go, no dragging down.

REGEXEXTRACT will attempt to pull "\S $" from each cell in B:B (that is, "all non-space characters to the end of the string"). If this cannot be found in any cell (e.g., a null cell), then IFERROR will return null instead of an error.

QUERY then acts on this, returning every 3rd entry for all non-null results.