CODEWITHSUNDEEP
google-sheetssplitgoogle-sheets-formula
Preack
Preack

Reputation: 123

Google Sheets Split Column into multiple columns

There's this formula that seems to work in this example:

=ARRAYFORMULA(VLOOKUP(TRANSPOSE(SEQUENCE(ROUNDUP(COUNTA(A2:A)/3),3,ROW(A2))),{ROW(A2:A),A2:A},2,0))

but when I try to use it for a range of 26 cells in a single column(K3:K28) to split it every 6 cells, using this formula:

=ARRAYFORMULA(VLOOKUP(TRANSPOSE(SEQUENCE(ROUNDUP(COUNTA(K3:K28)/6);6;ROW(K3)));{ROW(K3:K28);K3:K28};2;0))

it gives the error: VLOOKUP evaluates to an out-of-bounds range.

How to fix it??

Upvotes: 0

Views: 514

Answers (1)

Iamblichus
Iamblichus

Reputation: 19309

I'm thinking the problem is due to different syntaxes the different locales use. If you're using a locale that uses ; (e.g. Germany), the separator here:

{ROW(K3:K28);K3:K28}

Should be \ instead:

{ROW(K3:K28)\K3:K28}

So your formula should be:

=ARRAYFORMULA(VLOOKUP(TRANSPOSE(SEQUENCE(ROUNDUP(COUNTA(K3:K28)/6);6;ROW(K3)));{ROW(K3:K28)\K3:K28};2;0))

I would also use IFNA since 26 is not divisible by 6, so the last column will be incomplete:

=ARRAYFORMULA(IFNA(VLOOKUP(TRANSPOSE(SEQUENCE(ROUNDUP(COUNTA(K3:K28)/6);6;ROW(K3)));{ROW(K3:K28)\K3:K28};2;0)))

enter image description here

Upvotes: 1

Related Questions