Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with array formula | Excel Worksheet Functions | |||
Array Formula | Excel Worksheet Functions | |||
Need help troubleshooting an array formula XLXP on Win2K | Excel Worksheet Functions | |||
What instead of an array formula part 2 | Excel Discussion (Misc queries) |