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.

Hi kathi,

If the strings are always of the same length between commas as the example
you posted:
=LEFT(J1,1)
=MID(J1,6,1)
=MID(J1,10,1)
=MID(J1,13,1)

Cheers

--
macropod
[MVP - Microsoft Word]


"kathi" wrote in message
...
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.

This is excellent , but no they are not always the same.

cud b [ZD,OP,WER,I] or cud b [ZD5,O,KA] or cud b [ZD5]



"macropod" wrote:

Hi kathi,

If the strings are always of the same length between commas as the example
you posted:
=LEFT(J1,1)
=MID(J1,6,1)
=MID(J1,10,1)
=MID(J1,13,1)

Cheers

--
macropod
[MVP - Microsoft Word]


"kathi" wrote in message
...
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.

Hi Kathi

I don't know whether the space after the first comma is a typo, or will
always occur.
Assuming it will always occur, then in cell L1
=MID(J1,FIND(",",J1)+2,1)
change the +2 to +1 if the space was a typo

or a formula which will work either with or without the space
=LEFT(TRIM(MID(J1,FIND(",",J1)+1,2)))


in cell M1
=MID(J1,FIND("*",SUBSTITUTE(J1,",","*",2))+1,1)
in N1
=MID(J1,FIND("*",SUBSTITUTE(J1,",","*",3))+1,1)
--
Regards

Roger Govier


"kathi" wrote in message
...
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.

=MID(SUBSTITUTE(J1," ",""),FIND("~",SUBSTITUTE(SUBSTITUTE(J1,"
",""),",","~",1))+1,1)

=MID(SUBSTITUTE(J1," ",""),FIND("~",SUBSTITUTE(SUBSTITUTE(J1,"
",""),",","~",2))+1,1)

etc.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"kathi" wrote in message
...
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" 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.