=LEFT(J16,1)
 

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.

=LEFT(J16,1)
 

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.

=LEFT(J16,1)
 

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.

=LEFT(J16,1)
 

=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.

=LEFT(J16,1)
 

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.

=LEFT(J16,1)
 

"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.