I was amazed with the prompt reply from daddylonglegs, thank you if you are
reading this. I have altered your reply around and succeeded in getting the
names to come up how I wanted
Fred Bloggs
Freda Bloggs
etc
from my list (taken from a GEDCOM file) of many different lines.
I had to use your program (altered slightly) to extract them from the file I
had
=IF(COUNTIF(one!$A$1:one!$A$2940,"NAME*")ROW()-ROW(I$1),INDEX(one!$A$1:one!$A$2940,SMALL(IF(ISNUM BER(FIND("NAME",one!$A$1:one!$A$2940)),ROW(one!A$1 :one!$A$2940)-ROW(I$1)+1,""),ROW()-ROW(I$1)+1)),"")
the word 'one' is reading from sheet one of the spreadsheat into 'sheet1'
this gives me
NAME Fred \Bloggs\
every ten or so lines is a line like this.
copying this down the page gives me
NAME Fred \Bloggs\
NAME Freda \Bloggs\
etc
(Why do I use ctrl+shft+enter instead of just enter, I can see it makes a
difference, but why?)
I have managed to read this onto another line of the same sheet with
=MID($I1,SMALL(IF(MID(" "&$I1,ROW(INDIRECT("1:"&LEN($I1)+1)),1)="
",ROW(INDIRECT("1:"&LEN($I1)+1))),$H$1),SUM(SMALL( IF(MID(" "&$I1&"
",ROW(INDIRECT("1:"&LEN($I1)+2)),1)="
",ROW(INDIRECT("1:"&LEN($I1)+2))),$H$1+{0,1})* {-1,1})-1)
which gives me
Fred
Freda
etc
=RIGHT(I1,LEN(I1)-FIND("*",SUBSTITUTE(I1," ","*",LEN(I1)-LEN(SUBSTITUTE(I1,"
","")))))
which gives me
\Bloggs\
\Bloggs\
etc
Which with the addition of =SUBSTITUTE(C1, "/", "")
I have turned into
Bloggs
Bloggs
etc
and then moved these to another sheet so I get Surname in column one and
Christian name in column three (gap for spacing), (almost perfect for
printing).
my next challenge for anyone if you think you are up to it :-), is to
extract the line
DATE xxxx from the same list(where xxxx is a year, ie 1841). there is also
sometimes a DATE 16 mar 1841 (or any date) but not always, I need to ignore
these. so that I can put just the date (without the word DATE) in column five
for printing. I would love to just print this info from my start list. I
hope you appreciate the scarcasm :-)( ie the challenge :-) )but I am amazed
at the knowledge in this web site--
Thanks Bob