[ukchemist]

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]

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.