Array Formula

[frankybenali]

Cell A1 contains the text "TomJonAlf".

I am trying to separate the text into three text strings using an array
formula.

I have got so far but I am stuck now.

In cells A2 to A9 I have entered the array formula:

{=MID(A1,ROW(INDIRECT("1:"&LEN(A1))),3)}

This creates the following array:

Tom
omJ
mJo
Jon
onA
nAl
Alf
lf
f

I was hoping to use the following array formula to return Tom, Jon and
Alf:

{=INDEX(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),3),{1,4,7})}

But it just returns Tom. What am I doing wrong?

I know I could achieve this in a simpler way but I need to use the
results in a further step that I know demands an array.

Thanks

 

[Jason Morin]

Maybe...

=MID(A1,ROW(INDIRECT("1:3"))*3-2,3)

Array-entered.

HTH
Jason
Atlanta, GA