Explanation
Working from the inside out, the LEN function calculates the length of the string, and this is joined by concatenation to "1:", creating a text range like this: "1:3"
This text is passed into INDIRECT, which evaluates the text as a reference and returns the result to the ROW function. The ROW function returns the rows contained in the reference in an array of numbers like this:
{1;2;3}
Notice we have one number for each letter in the original text.
This array goes into the MID function, for the start_num argument. The text comes from column B, and the number of characters is hardcoded as 1
Finally, with multiple start numbers, MID returns multiple results in an array like this.
{"R";"e";"d"}
Related formulas
Create array of numbers
Strip non-numeric characters
Strip numeric characters from cell
Abbreviate names or words
Related functions
MID Function
The Excel MID function extracts a given number of characters from the middle of a supplied text string. For example, =MID("apple",2,3) returns "ppl".
ROW Function
The Excel ROW function returns the row number for a reference. For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula.
LEN Function
The Excel LEN function returns the length of a given text string as the number of characters. LEN will also count characters in numbers, but number formatting is not included.
INDIRECT Function
The Excel INDIRECT function returns a valid cell reference from a given text string. INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.
