Results 1 to 6 of 6
  1. #1
    Join Date
    May 2010
    Posts
    11

    Unanswered: Extract Text Help

    I can't find how to extract the last name from a field that has last name, first name. I want to extract everything prior to the comma and the last name are varying lengths so I can't use =left([saename],5)

    The field looks like this:

    Jones, Sara
    Crouse, John
    Millichecki, Tracy

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by tmcrouse View Post
    I can't find how to extract the last name from a field that has last name, first name. I want to extract everything prior to the comma and the last name are varying lengths so I can't use =left([saename],5)

    The field looks like this:

    Jones, Sara
    Crouse, John
    Millichecki, Tracy
    Try:

    =Trim(left([saename],InStr([saename],",")-1))
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    May 2010
    Posts
    601
    And first name would be:


    =Trim(Mid([saename],InStr([saename],",")+1))
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  4. #4
    Join Date
    May 2010
    Posts
    601
    To learn more about parsing, see my example here:

    Parsing out City State ZIP
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    May 2010
    Posts
    11

    Nevermind

    It doesn't work so I just went back to the Excel that was imported to Access that has lastname, firstname and made 2 new columns next to that and named one last and one first. Then I did Excel forumlas to split.

    =left(g2,find(",",g2)-1) and that gave me the left full name prior to comma

    then=right(g2,len(g2)-find(",",g2)-1) and that gave me full name after comma. Then just imported again to Access. Thanks!!!!

  6. #6
    Join Date
    May 2010
    Posts
    601
    Here is a test in the VBA Immediate Window:

    ? Trim(left("Jones, Sara", InStr("Jones, Sara",",")-1))
    Jones

    ? Trim(Mid("Jones, Sara", InStr("Jones, Sara",",") + 1))
    Sara


    How were you using this?

    =Trim(left([saename],InStr([saename],",")-1))

    I assume you were using this just display it on a form or report using a text box and setting the control source to be the "= ..."
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

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