Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Excel: extract surname from column with first name and sometimes initials (https://www.askmehelpdesk.com/showthread.php?t=284683)

  • Nov 24, 2008, 12:39 PM
    ukchemist
    Excel: extract surname from column with first name and sometimes initials
    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
  • Nov 24, 2008, 02:16 PM
    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.

  • All times are GMT -7. The time now is 12:52 AM.