Array Formula
 

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


--
frankybenali
------------------------------------------------------------------------
frankybenali's Profile: http://www.excelforum.com/member.php...o&userid=18707
View this thread: http://www.excelforum.com/showthread...hreadid=346006

Maybe...

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

Array-entered.

HTH
Jason
Atlanta, GA

-----Original Message-----

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


--
frankybenali
---------------------------------------------------------
---------------
frankybenali's Profile:
http://www.excelforum.com/member.php?
action=getinfo&userid=18707
View this thread:
http://www.excelforum.com/showthread...hreadid=346006

.