software

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 lt;=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(quot;,quot;amp;A1,seq,1)=quot;,quot;,seq),
ROW(INDIRECT(quot;1:quot;amp;COUNT(1,1/(MID(A1,seq,1)=quot;,quot;))))),
SMALL(IF(MID(A1amp;quot;,quot;,seq,1)=quot;,quot;,seq),ROW(INDIRECT(quot; 1:quot;amp;
COUNT(1,1/(MID(A1,seq,1)=quot;,quot;)))))-SMALL(IF(MID(quot;,quot;amp;A1,
seq,1)=quot;,quot;,seq),ROW(INDIRECT(quot;1:quot;amp;COUNT(1,1/(MID(A1,
seq,1)=quot;,quot;))))))

where seq is a named formula quot;=ROW(INDIRECT(quot;1:1024quot;)) ... 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(quot;~quot;,SUBSTITUTE(A2,quot;-quot;,quot;~quot;,2))

where quot;~quot; is ANY OTHER CHARACTER EXCEPT what you are looking for
and quot;-quot; 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?''.quot;Christopher Shortquot; wrote:

gt; I have a set of data in a cell that is comma delimited (more specifically -
gt; a range of cells).
gt;
gt; I want to select the nth item in each string (where n lt;=4).
gt;
gt; I can do this with nested if statements together with various
gt; find/mid/left/right functions - however, by the time I format it for
gt; readability it's 411 characters long - and I know that users will look at it
gt; and scratch their heads each time - even with clear comments.
gt;
gt; I'm looking to confirm that there is no function in Excel (2003) that could
gt; do this in a more transparent manner - such as index - which won't work
gt; because I want the nth item in a delimited string - rather than a range or
gt; an array.
gt;
gt; For transparency, probably the best thing is to use functions to parse the
gt; string into the next 4 columns and then use index functions on those
gt; columns.
gt;
gt; oh - just found Harlan's (or is Frank Kabel's) trick of parsing a CSV string
gt; (in cell A1) to an array via an array formula:
gt;
gt; =--MID(A1,SMALL(IF(MID(quot;,quot;amp;A1,seq,1)=quot;,quot;,seq),
gt; ROW(INDIRECT(quot;1:quot;amp;COUNT(1,1/(MID(A1,seq,1)=quot;,quot;))))),
gt; SMALL(IF(MID(A1amp;quot;,quot;,seq,1)=quot;,quot;,seq),ROW(INDIRECT(quot; 1:quot;amp;
gt; COUNT(1,1/(MID(A1,seq,1)=quot;,quot;)))))-SMALL(IF(MID(quot;,quot;amp;A1,
gt; seq,1)=quot;,quot;,seq),ROW(INDIRECT(quot;1:quot;amp;COUNT(1,1/(MID(A1,
gt; seq,1)=quot;,quot;))))))
gt;
gt; where seq is a named formula quot;=ROW(INDIRECT(quot;1:1024quot;)) ... 1024 can be
gt; reduced if you know the maximum number of characters is less than 1024.
gt;
gt; (And it's 285 characters and not formatted for readabiliy :-(
gt;
gt; So presumably the answer to the question is - parsing comma separated
gt; strings via worksheet functions can only be done with 'hard to audit'
gt; formulas!
gt;
gt; thanks,
gt; Christopher
gt;
gt;
gt;