"kathi" skrev i en meddelelse
...
I have a cell that contains the following in column J
ZD5, MNH,IX,WQ
I need to have the first letter of the first group in column K so I put
the
formula [=LEFT(J1,1)] and that works but I can't figure out a formula for
column L for the first letter of the second group (MNH) or for column M
for
the first letter of the third group, or for column N for the first letter
of
the fourth group.
Anyone know how to do this? I can change the divider between groups to a
comma, a hyphen-, a semi-colon; or a colon: or watever is necessary so
that
it can be used as an indicator in the formula.
Thanks for any help you can be.

kathi

Make the divider a single comma, then enter this array formula in K1

=MID($J1,SMALL(IF(MID(","&$J1,ROW(INDIRECT("1:"&LE N($J1))),1)=",",
ROW(INDIRECT("1:"&LEN($J1)))),COLUMN()-COLUMN($K$1)+1),1)

Commit with <Shift<Ctrl<Enter, also if you edit the formula later.

Copy K1 to L1:N1 with the fill-handler (the little square in the lower
right corner of the cell).

Copy K1:N1 down if necessary.

The number of groups in J1 may be expanded at will, just copy K1 the
appropiate number of cells to the right.


--
Best regards
Leo Heuser

Followup to newsgroup only please.