# WRAPCOLS in Google Sheets

2022-09-16 • 2 min read

In this article I will explain how to implement the Excel WRAPCOLS() function in Google Sheets.

# Named Function

## Description

Wraps the provided array of values by columns after a specified number of elements to form a new array. The provided array is first converted to a vector.

## Syntax

`_WRAPCOLS(vector, wrap_count, pad_with)`

## Example

`_WRAPCOLS(A1:A10, 3, "x")`

## Formula Definition

`=ARRAYFORMULA(IFNA(VLOOKUP(SEQUENCE(wrap_count,CEILING(ROWS(FLATTEN(vector))/wrap_count)),{SEQUENCE(ROWS(FLATTEN(vector))),FLATTEN(vector)},2,0),pad_with))`

- vector - The array to wrap.
- wrap_count - The maximum number of values for each column.
- pad_with - The value with which to pad.

# Explanation

## Step 1 - Assign a numerical value to each element of the array

The first step is to assign a unique value to each element of the array. This is done using the SEQUENCE() function.

`=SEQUENCE(ROWS(A1:A11))`

## Step 2 - Create a wrap_count × n array

The second step is to create a two-dimensional array of sequential numbers that has wrap_count rows and array_size/wrap_count (rounded to the next integer) columns. This is also done using the SEQUENCE function.

For this example I will assume wrap_count to be 3.

`=SEQUENCE(3,CEILING(ROWS(A1:A11)/3))`

## Step 3 - Run a VLOOKUP()

The final step is to run a vertical lookup with the array from the previous step as the search_key, the array from Step 1 as the search column and the input array as the return column.

`=ARRAYFORMULA(IFNA(VLOOKUP(SEQUENCE(3,CEILING(ROWS(A1:A11)/3)),{SEQUENCE(ROWS(A1:A11)),A1:A11},2,0),"x"))`

The VLOOKUP is wrapped in an IFNA()which acts as the pad_with parameter of the Named Function.

## Note

The Named Function has a FLATTEN() wrapper on every occurrence of the input array. This is done to ensure that the formula works even if the array is not vertical.

That's all. Thanks for reading.