Summary
The Excel WRAPCOLS function is designed to convert a one-dimensional array or range into a two-dimensional array by wrapping values into separate columns. This function is particularly useful for reorganizing linear data into a more structured format, which can be essential for presentation or specific types of data analysis.
Syntax
=WRAPCOLS(vector, wrap_count, [pad_with])
- vector: The one-dimensional array or range to wrap
- wrap_count: The maximum number of values in each column. When this count is reached, WRAPCOLS starts a new column
- [pad_with]: [Optional] Value to use for filling any unfilled places in the final column
Return value
A two-dimensional array with values wrapped by column.
How to use
Use WRAPCOLS by specifying the vector (one-dimensional array or range) and the wrap_count, which determines the number of values per column. Optionally, specify pad_with to fill any unfilled spots in the final column.
Examples
Simple WRAPCOLS
Wrapping an Array into Columns: To convert a horizontal range into columns with a specific number of rows:
=WRAPCOLS(A1:J1, 4)
This formula wraps the one-dimensional range A1:J1 into columns, each containing up to 4 values.
WRAPCOLS with Different Wrap Counts
Varying Column Lengths in Wrapped Data: Adjusting the number of rows in each column:
=WRAPCOLS(A1:J1, 3)
This formula converts the range A1:J1 into columns with up to 3 values each.
WRAPCOLS with Padding
Using Padding to Fill Empty Spaces: To fill empty spaces with a specific value:
=WRAPCOLS(A1:J1, 4, "Empty")
This wraps the range A1:J1 into columns of up to 4 rows each, using “Empty” to fill any unfilled spots in the last column.
WRAPCOLS for Vertical Data
Wrapping a Vertical Range into Columns: Converting a vertical range into a structured column format:
=WRAPCOLS(A1:A10, 2)
This formula takes the vertical range A1:A10 and wraps it into columns, each with up to 2 values.
Advanced WRAPCOLS
Complex Reorganization of Data with WRAPCOLS: To reorganize data into columns with custom padding:
=WRAPCOLS(A1:A12, 5, "N/A")
Converts the vertical range A1:A12 into columns with up to 5 values each, using “N/A” to fill any gaps in the final column.
Additional Notes
- WRAPCOLS is a powerful tool for transforming linear data sets into a structured columnar format.
- The function automatically starts a new column after reaching the specified wrap_count.
- WRAPCOLS will return a #VALUE! error if the provided vector is not a one-dimensional array or range.