ukchemist
Nov 24, 2008, 12:39 PM
In an Excel spreadsheet I have a column containing first name, sometimes an initial (or two), then a surname then in brackets a date of birth. How can I extract the surname?
e.g. one row might be John A Smith (01/01/1990)
The next might be John A Smith (02/02/1991) and the next
John Smith (03/03/1992)
All help very gratefully received
ebaines
Nov 24, 2008, 02:16 PM
The following assumes that's there's a maximum to the number of words that can make up a name (i.e. never more than 4), and that you don't need to worry about suffixes like "Jr" coming after the last name. Starting with a string in cell A1:
1. First, strip off the date string at the end - (i.e, a string starts with a spce and left parenthesis) and put this in cell A2:
=LEFT(A1,SEARCH(" (",A1)-1)
You now have the name by itself.
2. Now we're going to count how many spaces there are in the string in cell A2,and put this value in A3:
=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ","")))
3. If this number in A3 is >0, then search for the first instance of a space in the string in A2, strip off the leading word and storewhatever is to the right of that in cell A4:
=RIGHT(a2,LEN(a2)-IF(a3>0,SEARCH(")))
3. If this number in A3 is >0, then search for the first instance of a space in the string in A2, strip off the leading word and storewhatever is to the right of that in cell A4:
=RIGHT(a2,LEN(a2)-IF(a3>0,SEARCH(",a2),0))
Repeat steps 2 and 3 four times, changing references to cells A2 and A3 to cells A4and A5, etc. As long as there aren't more than 4 words in any name, you'll end up with just the last name in cell A12.
You could probably combine all this into one massive command. Hope this helps.