splitting forenames/surnames into separate columns

[Bill_OR]

Hi,
I'm trying to split up some text strings into columns. The text is of the
form "father_forename1 father_forename2 [etc.] & mother_forename1
mother_forename2 [etc.] surname" where there may be a variable number of
forenames - (it's the names of the parents in a baptism record). I want to
split the text into three columns: fathers forenames, mothers fornames and
surname.
I can split off the father's forenames using text to columns (splitting at
the &) but I am struggling to find a way to isolate the surname from the
mother's forenames.
Ideally I would like to be able to use an equivalent of the FIND or SEARCH
function but I need to find/search for the first space starting from the end
of the string - whereas these functions start from the beginning of the
string.
Any suggestions?

 

[Ron Rosenfeld]

Hi,
I'm trying to split up some text strings into columns. The text is of the
form "father_forename1 father_forename2 [etc.] & mother_forename1
mother_forename2 [etc.] surname" where there may be a variable number of
forenames - (it's the names of the parents in a baptism record). I want to
split the text into three columns: fathers forenames, mothers fornames and
surname.
I can split off the father's forenames using text to columns (splitting at
the &) but I am struggling to find a way to isolate the surname from the
mother's forenames.
Ideally I would like to be able to use an equivalent of the FIND or SEARCH
function but I need to find/search for the first space starting from the end
of the string - whereas these functions start from the beginning of the
string.
Any suggestions?

If the surname is a single word, then:

A1:
father_forename1 father_forename2 [etc.] & mother_forename1 mother_forename2
[etc.] surname

B1: Return everything up to the "&"
=TRIM(LEFT(A1,FIND("&",A1)-1))

C1: Return all after the "&" but not last word:
=TRIM(MID(TRIM(A1),FIND("&",TRIM(A1))+1,FIND(CHAR(1),
SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(
SUBSTITUTE(TRIM(A1)," ",""))))-FIND("&",TRIM(A1))))

D1: Return last word
=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99))

--ron

 

[Bill_OR]

Thanks Ron,
I'll work through the logic to understand what you've done and then give it
a whirl!
Thanks for your help.
regards,
Bill

Hi,
I'm trying to split up some text strings into columns. The text is of the
form "father_forename1 father_forename2 [etc.] & mother_forename1
mother_forename2 [etc.] surname" where there may be a variable number of
forenames - (it's the names of the parents in a baptism record). I want to
split the text into three columns: fathers forenames, mothers fornames and
surname.
I can split off the father's forenames using text to columns (splitting at
the &) but I am struggling to find a way to isolate the surname from the
mother's forenames.
Ideally I would like to be able to use an equivalent of the FIND or SEARCH
function but I need to find/search for the first space starting from the end
of the string - whereas these functions start from the beginning of the
string.
Any suggestions?

If the surname is a single word, then:

A1:
father_forename1 father_forename2 [etc.] & mother_forename1 mother_forename2
[etc.] surname

B1: Return everything up to the "&"
=TRIM(LEFT(A1,FIND("&",A1)-1))

C1: Return all after the "&" but not last word:
=TRIM(MID(TRIM(A1),FIND("&",TRIM(A1))+1,FIND(CHAR(1),
SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(
SUBSTITUTE(TRIM(A1)," ",""))))-FIND("&",TRIM(A1))))

D1: Return last word
=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99))

--ron