Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    48

    Question Unanswered: Displaying or extracting characters preceeding a comma

    I should know this but...
    What expression will produce all the characters in a string that precede a comma. Also, what expression will produce all of the characters that follow the comma. Example: If the string is "Smith, Bill", I need the expression that will produce "Smith" and the expression that will produce "Bill". Thanks for any help offered.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the string manipulation functions such as instr, left$, right$ & mid$

  3. #3
    Join Date
    Aug 2004
    Posts
    48

    displaying or extracting characters up to or after a comma

    Thanks "healdem". If I knew that all of the last names in the field were 5 characters long I could use Left([fieldname],5) or Mid([Fieldname],1,5) but the last names and first names are all of different lengths so I'm trying to figure out the proper syntax for "take all of the text up to the comma" and/or "take all of the text after the comma". Any additional help will be appreciated.

  4. #4
    Join Date
    Feb 2005
    Location
    Princeton, NJ USA
    Posts
    13

    Use InStrRev function

    Try the function InStrRev to locate the comma and then you can parse the string into the two components:
    strName is the lastname, firstname

    intComma = InStrRev(strName, "," , Len(strName))

    This should give you the length of the last name so you can then Left(strName, intComma) to get just the last name.
    Then figure out the length of StrName and subtract intComma (probably plus 2 more to account for the comma and the space) and use the Right function to get the first name.

    You have to be careful with how the data is entered here - since some names can contain commas before suffixes (Jr., III, M.D. etc.)
    Hope this helps!

  5. #5
    Join Date
    Oct 2003
    Posts
    6

    Use Split function

    Checkout the Split function in VBA help. I've used this in VB code to split names into two strings. You can use "," as your delimeter and specify 2 splits (or use -1 to get all splits). As mentioned before, be cautious of names containing a ","

  6. #6
    Join Date
    Aug 2004
    Posts
    48

    Extracting or displaying characters up to a comma

    Thanks to Allen Olsen and D Yount for their suggestions. I'll give them a try.

Posting Permissions

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