MID & LEFT??

[Tony]

I have a spreadsheet that has several columns that contain
multipule names in each cell. Some cells have two names
and some have four names. Each name is followed by a
comma and a soft return.

I want to extract the last row of this worksheet and
spread out each name over the number of cells needed for
the names i.e. one cell (A) has parents names (2 each)
I would like my second sheet to have the Father's name in
a cell seperate from the Mother's name.

I know that I could do a macro with text to columns but I
wanted to have a formula that would automatically seperate
them. I've tried MID and it works for the Parents but I
have a column with four names. When I try using MID I can
extract the first and second name but not the third and
fourth name. Could I use a combination of MID and LEFT?

Any help would be appreciated.

Thanks

 

[Bob Phillips]

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=3,LEFT(CLEAN(A1),FIND(" ",A1,FIND("
",A1)+1)-1),LEFT(CLEAN(A1),FIND(A1," ")-1))
and
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=3,MID(CLEAN(A1),FIND(" ",A1,FIND("
",A1)+1)+1,255),MID(CLEAN(A1),FIND(" ",A1)+1,255))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)