Not sure what your question is?

If you use the Evaluate formula tool found in the Formulas tab, you will see how it breaks out.

If you have 13579, then ROW(INDIRECT("1:"&LEN(A1))) breaks down to: ROW(INDIRECT("1:"&5)) where 5 is the length of the text 13579. That further breaks to ROW($1:$5) and when applied to MID(A1,ROW($A$1:$A5),1), it expands out to MID(A1,{1,2,3,4,5},1) so now each of the five characters in A1 gets extracted by the MID function so you get {"1","3","5","7","9"} and each gets concatenated to the word "Sheet", even if there is a 0 in the number... there is another SUBSTITUTE added to clear out any "Sheet0" that may be included, leaving only the desired text string.