=LEFT(J16,1)

[Guest]

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.

 

[macropod]

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

 

[Roger Govier]

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)

 

[Bob Phillips]

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

 

[Guest]

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]

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]

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.

 

[Leo Heuser]

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:"&LEN($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.