Here, we will learn how to use the Excel dynamic array function: EXPAND. Later, we will also learn how to split text into expanded array using EXPAND and the TEXTSPLIT function, that splits text by the given delimiter.
EXPAND function
The Excel EXPAND function expands the given array to the given number of rows and columns using the given value.
EXPAND takes four arguments: array, [rows], [columns], [pad_with]. The first required argument array will take the range to expand. Both rows and columns arguments are optional, but a numeric value is required for at least one. Rows will take the final number of rows to expand the new array. If omitted, no new rows are added and columns must be set. Columns will take the final number of columns to expand the new array. If omitted, no new columns are added and rows must be set. The final optional argument pad_with will take the value to use for new cells. If omitted, the #N/A error will be used for new cells.
Basic examples
To expand an array to be 7 rows by 4 columns, use EXPAND like this:
=EXPAND(A4:C8,7,4)
Here, since we omit the pad_with argument, EXPAND will fill the new cells with the #N/A error.
To expand an array to be 7 rows by 4 columns, and fill the new cells with "#", use the EXPAND formula (in E4 shown in the image) like this:
=EXPAND(A4:C8,7,4,"#")
Splitting text into expanded array using EXPAND with TEXTSPLIT
To split text into expanded array, use the EXPAND formula and nest the TEXTSPLIT function in the array argument of EXPAND.
The dataset (shown in the image) contains the data about different products in column A. The product ID, product name, type of product and the amount each is separated by the comma and the space (, ) in the "Product data" column. Also, some products' prices have not been mentioned in the data. The goal is to expand the data to create separate columns for each: "P_ID", "Product", "Type" and "Amount" for better readability. Also, a hyphen needs to be added in the "Amount" column for those products whose prices are unknown.
The formula in E14, copied down, is:
=EXPAND(TEXTSPLIT(A14,", "),,4,"-")
As soon as I press Enter, the formula spills the results into the multiple cells automatically in the range E14:H14.
How this formula works
TEXTSPLIT splits the text "P_101, OnePlus 12R, Phone, 6200" by the delimiter ", " spilling into multiple cells in the range E14:H14. EXPAND expands the array by adding (default 8 rows) 4 columns and fills the new cells with the hyphen "-".
You have successfully learnt how to use the Excel dynamic array function: EXPAND, and how to split text into expanded array using EXPAND and the TEXTSPLIT function! I hope this post helped you.
