The result is a horizontal array with nine characters that spills into the range D5:L5. Typically, the MID function is used to extract one or more characters from a text string. So, for example, you could use MID like this: The trick in this formula is to ask MID for all characters in the text string by providing an array for the start_num argument. For example, to ask for the three letters in “red”, we can use an array like this: To generate the array we need for num_chars we use the SEQUENCE function. Turning back to the example shown, we have this formula in cell D5: Working from the inside out, the LEN function is used to calculate the number of characters in cell B5, which is 9. Dropping that value into the formula, we have: With 1 for the rows argument and 9 for columns*, SEQUENCE returns a numeric array beginning with 1 and ending with 9. This array is delivered to the MID function as the start_num: The final result from MID is an array like this: When this array lands in cell D5, it spills into the range D5:L5. Notice the final array is comma-separated, which corresponds to a horizontal array or range in Excel. *The reason we get a horizontal array in columns instead of a vertical array in rows is because we configured SEQUENCE to ask for columns instead of rows.
Legacy Excel
In Legacy Excel, which lacks dynamic arrays and the SEQUENCE function, it is more challenging to split a text string to an array of characters. However one workaround is to use a simple formula like this, copied into the range D5:P15: The result looks like this:
This works because we can use the numbers in the range D4:P4 directly as start_num inside the MID function. Notice that $B5 and D$4 are both mixed references, so that the formula can be copied throughout the range. Compared to the dynamic array solution above, the big difference in this approach is that results will not automatically spill onto the worksheet into a spill range. Instead, the formulas must be manually copied into a range of cells big enough to hold all characters. Note that you can split a text string into a character array in Legacy Excel with a more complicated formula: This formula uses the ROW function with the INDIRECT function to create a vertical numeric array like SEQUENCE above: The INDIRECT function is a way of creating a valid Excel reference using text. The ROW function then returns an array of row numbers that correspond to the reference created by INDIRECT. The final result (after TRANSPOSE) is a horizontal array like the original formula above. This array won’t spill in older versions of Excel, and entering the formula as a multi-cell array formula is tedious. However, this approach still has value in older versions of Excel in formulas that don’t need to spill multiple values. For an example, see: Count numbers in a text string.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
