Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
splitting forenames/surnames into separate columns
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
splitting forenames/surnames into separate columns
On Sat, 30 Aug 2008 02:57:01 -0700, Bill_OR
wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
splitting forenames/surnames into separate columns
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 "Ron Rosenfeld" wrote: On Sat, 30 Aug 2008 02:57:01 -0700, Bill_OR wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract surname from forenames | Excel Discussion (Misc queries) | |||
I wish to separate city, state, and zip into 3 separate columns | Excel Worksheet Functions | |||
Splitting a concatenated string into separate rows... | Excel Worksheet Functions | |||
splitting first and surnames | Excel Discussion (Misc queries) | |||
Splitting Data into separate sheets | Excel Discussion (Misc queries) |