I have a set of data in a cell that is comma delimited (more specifically -
a range of cells).

I want to select the nth item in each string (where n <=4).

I can do this with nested if statements together with various
find/mid/left/right functions - however, by the time I format it for
readability it's 411 characters long - and I know that users will look at it
and scratch their heads each time - even with clear comments.

I'm looking to confirm that there is no function in Excel (2003) that could
do this in a more transparent manner - such as index - which won't work
because I want the nth item in a delimited string - rather than a range or
an array.

For transparency, probably the best thing is to use functions to parse the
string into the next 4 columns and then use index functions on those
columns.

oh - just found Harlan's (or is Frank Kabel's) trick of parsing a CSV string
(in cell A1) to an array via an array formula:

=--MID(A1,SMALL(IF(MID(","&A1,seq,1)=",",seq),
ROW(INDIRECT("1:"&COUNT(1,1/(MID(A1,seq,1)=","))))),
SMALL(IF(MID(A1&",",seq,1)=",",seq),ROW(INDIRECT(" 1:"&
COUNT(1,1/(MID(A1,seq,1)=",")))))-SMALL(IF(MID(","&A1,
seq,1)=",",seq),ROW(INDIRECT("1:"&COUNT(1,1/(MID(A1,
seq,1)=","))))))

where seq is a named formula "=ROW(INDIRECT("1:1024")) ... 1024 can be
reduced if you know the maximum number of characters is less than 1024.

(And it's 285 characters and not formatted for readabiliy :-(

So presumably the answer to the question is - parsing comma separated
strings via worksheet functions can only be done with 'hard to audit'
formulas!

thanks,
Christopher

'/===========================================/
To Find the nth position of a character in a cell, use...

=FIND("~",SUBSTITUTE(A2,"-","~",2))

where "~" is ANY OTHER CHARACTER EXCEPT what you are looking for
and "-" is the character you ARE looking for
and 2 is the instance number

Syntax of Substitute -
SUBSTITUTE(text,old_text,new_text,instance_num)
'/===========================================/

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Christopher Short" wrote:

I have a set of data in a cell that is comma delimited (more specifically -
a range of cells).

I want to select the nth item in each string (where n <=4).

I can do this with nested if statements together with various
find/mid/left/right functions - however, by the time I format it for
readability it's 411 characters long - and I know that users will look at it
and scratch their heads each time - even with clear comments.

I'm looking to confirm that there is no function in Excel (2003) that could
do this in a more transparent manner - such as index - which won't work
because I want the nth item in a delimited string - rather than a range or
an array.

For transparency, probably the best thing is to use functions to parse the
string into the next 4 columns and then use index functions on those
columns.

oh - just found Harlan's (or is Frank Kabel's) trick of parsing a CSV string
(in cell A1) to an array via an array formula:

=--MID(A1,SMALL(IF(MID(","&A1,seq,1)=",",seq),
ROW(INDIRECT("1:"&COUNT(1,1/(MID(A1,seq,1)=","))))),
SMALL(IF(MID(A1&",",seq,1)=",",seq),ROW(INDIRECT(" 1:"&
COUNT(1,1/(MID(A1,seq,1)=",")))))-SMALL(IF(MID(","&A1,
seq,1)=",",seq),ROW(INDIRECT("1:"&COUNT(1,1/(MID(A1,
seq,1)=","))))))

where seq is a named formula "=ROW(INDIRECT("1:1024")) ... 1024 can be
reduced if you know the maximum number of characters is less than 1024.

(And it's 285 characters and not formatted for readabiliy :-(

So presumably the answer to the question is - parsing comma separated
strings via worksheet functions can only be done with 'hard to audit'
formulas!

thanks,
Christopher