Split text string to character array

In this example, the goal is to use a formula to split a text string into an array of characters. 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 currently no way to ask TEXTSPLIT to split a string into separate characters. Likewise, there is no function in Excel dedicated to this task. One workaround is to use the SEQUENCE function with the MID function like this:

=MID(B5,SEQUENCE(1,LEN(B5)),1)

Before we get into the specifics of how this formula works, let's step back a moment and consider how the MID function is designed to work. Typically, the MID function is used to extract one or more characters from a text string. For example, you can 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"

Depending on the value given for start_num and num_chars, we get a different portion of the string. However, in this case, we don't want to return parts of the text string, we want to return all the characters in the text string together in an array. The trick is to ask the MID function for more than one start_num at the same time. For example, to return the three letters in "red" in an array, we can use an array constant like this:

=MID("red",{1,2,3},1) // returns {"r","e","d"}

Because we are asking MID for 1 character starting at three positions {1,2,3}, we get back all three letters in "red" in a single array. So, this approach works, but we need a way to dynamically generate an array of sequential numbers that corresponds to the length of the string. Enter the SEQUENCE function, which is perfectly designed for this task. 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 now 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, with num_chars set to 1:

=MID(B5,{1,2,3,4,5,6,7,8,9},1)

In other words, we are asking MID for 1 character starting at 9 different positions, one for each character in the source text. In this configuration, MID dutifully extracts all 9 characters in delivers the result in 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 that 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. One workaround is to use the ROW and INDIRECT functions in place of SEQUENCE:

=MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)

This formula uses the ROW function with the INDIRECT function to create a 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 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.

String to range

If your goal is to extract the characters in a text string into separate cells in a range, you can take a different approach with a simple formula like this, copied into the range D5:P15 of the worksheet below:

=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, one 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.