+ Reply to Thread
Results 1 to 12 of 12

Trying to extract First and Last Names from a string

  1. #1
    Registered User
    Join Date
    04-30-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    3

    Trying to extract First and Last Names from a string

    I work for a law office, and we're provided with the full case caption in a cell, but I need to extract the data for mailing purposes. I've already figured out the functions for pulling addresses, but for some reason, I'm having a heck of a time getting the formula right to extract the first and last names of individuals from the following types of cell data (all names have been changed):

    WELLS FARGO BANK, N.A. vs. SMITH, MAUREEN
    THE BANK OF NEW YORK MELLON vs. JONES, GEORGIA
    WELLS FARGO BANK, N.A. vs. ADAMS, AINSLEY
    BAYVIEW LOAN SERVICING, LLC vs. WILSON, LUIS H.
    CITIBANK, N.A. vs. SULLIVAN, MATTHEW
    NATIONSTAR MORTGAGE, LLC vs. WILLIAMS, RONALD W.

    Each of the above captions are currently in their own individual cell on my sheet.


    Thanks!

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Trying to extract First and Last Names from a string

    How is your data organized? it's hard to see from example since formatting is lost.

  3. #3
    Valued Forum Contributor
    Join Date
    09-01-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    324

    Re: Trying to extract First and Last Names from a string

    Try this: (Updated . to vs.). Will remove everything before vs.

    Paste in B2 (assuming data starts in A1):
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by playaller; 04-30-2014 at 05:23 PM.


    Shelton A.
    If Helpful, Add Reputaion!

  4. #4
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Trying to extract First and Last Names from a string

    For the first names:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    And for the surnames:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-30-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Trying to extract First and Last Names from a string

    This worked perfect, except that for the surnames, it's placing 1-2 spaces in front of the name?

    Quote Originally Posted by gak67 View Post
    For the first names:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    And for the surnames:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor
    Join Date
    09-01-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    324

    Re: Trying to extract First and Last Names from a string

    If you don't mind the name in one cell:

    Paste in B1 and pull down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Trying to extract First and Last Names from a string

    It didn't with the sample data you provided that I pasted into a sheet. Is there sometimes more than one space between the vs. and the surname?

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Trying to extract First and Last Names from a string

    Try this...

    Data Range
    A
    B
    2
    WELLS FARGO BANK, N.A. vs. SMITH, MAUREEN
    SMITH, MAUREEN
    3
    THE BANK OF NEW YORK MELLON vs. JONES, GEORGIA
    JONES, GEORGIA
    4
    WELLS FARGO BANK, N.A. vs. ADAMS, AINSLEY
    ADAMS, AINSLEY
    5
    BAYVIEW LOAN SERVICING, LLC vs. WILSON, LUIS H.
    WILSON, LUIS H.
    6
    CITIBANK, N.A. vs. SULLIVAN, MATTHEW
    SULLIVAN, MATTHEW
    7
    NATIONSTAR MORTGAGE, LLC vs. WILLIAMS, RONALD W.
    WILLIAMS, RONALD W.


    This formula entered in B2 and copied down:

    =MID(A2,SEARCH("vs.",A2)+4,100)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Trying to extract First and Last Names from a string

    If you want the names extracted in reverse order:

    Data Range
    A
    B
    2
    WELLS FARGO BANK, N.A. vs. SMITH, MAUREEN
    MAUREEN SMITH
    3
    THE BANK OF NEW YORK MELLON vs. JONES, GEORGIA
    GEORGIA JONES
    4
    WELLS FARGO BANK, N.A. vs. ADAMS, AINSLEY
    AINSLEY ADAMS
    5
    BAYVIEW LOAN SERVICING, LLC vs. WILSON, LUIS H.
    LUIS H. WILSON
    6
    CITIBANK, N.A. vs. SULLIVAN, MATTHEW
    MATTHEW SULLIVAN
    7
    NATIONSTAR MORTGAGE, LLC vs. WILLIAMS, RONALD W.
    RONALD W. WILLIAMS


    This formula entered in B2 and copied down:

    =MID(MID(A2,SEARCH("vs.",A2)+4,100)&" "&
    MID(A2,SEARCH("vs.",A2)+4,100),FIND(",",MID(A2,
    SEARCH("vs.",A2)+4,100))+2,LEN(MID(A2,SEARCH("vs.",A2)+4,100))-1)

  10. #10
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Trying to extract First and Last Names from a string

    Or this

    =SUBSTITUTE(A1,LEFT(A1,FIND(",",A1,FIND("vs",A1))+1),"")&" "&LEFT(REPLACE(A1,1,FIND("vs",A1)+3,""),FIND(",",MID(A1,SEARCH("vs.",A1)+4,LEN(A1)))-1)

    A
    B
    1
    WELLS FARGO BANK, N.A. vs. SMITH, MAUREEN MAUREEN SMITH
    2
    THE BANK OF NEW YORK MELLON vs. JONES, GEORGIA GEORGIA JONES
    3
    WELLS FARGO BANK, N.A. vs. ADAMS, AINSLEY AINSLEY ADAMS
    4
    BAYVIEW LOAN SERVICING, LLC vs. WILSON, LUIS H. LUIS H. WILSON
    5
    CITIBANK, N.A. vs. SULLIVAN, MATTHEW MATTHEW SULLIVAN
    6
    NATIONSTAR MORTGAGE, LLC vs. WILLIAMS, RONALD W. RONALD W. WILLIAMS
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  11. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Trying to extract First and Last Names from a string

    Thank you for the feedback!

    Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Trying to extract First and Last Names from a string

    Thanks for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to extract names from a text string?
    By Bandini in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-16-2012, 09:51 AM
  2. [SOLVED] Extract a mainly numeric string (ISSN number) from an arbitrary string
    By Buzzed Aldrin in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 07-04-2012, 01:49 PM
  3. Replies: 16
    Last Post: 10-11-2011, 12:31 PM
  4. Extract small string of text from larger string
    By mark_jam3s in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-23-2010, 05:36 AM
  5. EXTRACT TEXT FROM TEXT STRING:The names are of variable length
    By carricka in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-08-2005, 06:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1