Explanation
In this example, the goal is to use a formula to split a text string into a character array. So, for example, if the text string is "Apple", the resulting array should be {"A","p","p","l","e"}. Although the newer TEXTSPLIT function is designed to split text strings into arrays with a custom delimiter, there is not a function that will split text into individual characters in one step. One workaround is to use the SEQUENCE function with the MID function like this:
=MID(B5,SEQUENCE(1,LEN(B5)),1)
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:
=MID("12 apples",1,1) // returns "1"
=MID("12 apples",1,2) // returns "12"
=MID("12 apples",3,6) // returns "apple"
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:
=MID("red",{1,2,3},1) // returns {"r","e","d"}
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:
=MID(B5,SEQUENCE(1,LEN(B5)),1)
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:
=MID(B5,SEQUENCE(1,9),1)
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:
=MID(B5,{1,2,3,4,5,6,7,8,9},1)
The final result from MID is an array like this:
{"1","2"," ","a","p","p","l","e","s"}
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:
=MID($B5,D$4,1)
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:
=TRANSPOSE(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))
This formula uses the ROW function with the INDIRECT function to create a vertical numeric array like SEQUENCE above:
=ROW(INDIRECT("1:"&LEN(B5)))
=ROW(INDIRECT("1:"&9))
=ROW(INDIRECT("1:9"))
={1;2;3;4;5;6;7;8;9}
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.
