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? |
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 |
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 |
All times are GMT +1. The time now is 01:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com