Results 1 to 5 of 5

Thread: String question

  1. #1
    Join Date
    Jun 2002
    Posts
    72

    Unanswered: String question

    What's the correct VBA code for extracting the last word in a string?
    For example, I want to extract the "SMITH" out of "FRED SMITH".

  2. #2
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109
    You'll have to use the MID$ and INSTR function. Look for Access VBA help on these function. You'll have to create a funtion. The following example will not work everytime. It will only work if the name ALWAYS have a space in it, and is composed of only TWO names, but still it shows the usage of the functions

    Code:
        Dim strString As String
        strString = "Fred Smith"
            
        'This only get the right part after the space -_> " "
        MsgBox Mid$(strString, InStr(1, strString, " "))


    JefB

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    Try the following:

    Dim strTest as string
    Dim strRev as string

    strTest = "This old house"
    strRev = StrReverse(strTest)

    strTest = StrReverse(Left(strRev, InStr(1, strRev, " ") - 1))

  4. #4
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84

    Re: String question

    Originally posted by Spanner
    What's the correct VBA code for extracting the last word in a string?
    For example, I want to extract the "SMITH" out of "FRED SMITH".
    You had two other responses that may work, but there is a VBA function that should do the job nicely with more clarity.

    DIM strString as String
    DIM strLastWord as String

    strString = "Fred Smith"

    strLastWord = InstrRev(strString, " ")

    strLastWord should be "Smith" after InstrRev is executed.
    Regards,
    Terry

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    Almost, but not quite there - the InstrRev returns a position not a string.

    Dim strTest as string
    Dim strRev as string

    strTest = "This old house"
    strRev = StrReverse(strTest)

    'NEW CODE
    strTest = Right(strTest, Len(strTest) - InstrRev(strTest, " "))
    'versus
    strTest = StrReverse(Left(strRev, InStr(1, strRev, " ") - 1))

    If you really wanted to make it easy, use the split function:

    Dim strArray as variant
    Dim strTest as string

    strArray = Split(StrReverse(strTest)," ")
    'this will guarantee that the last element you want is always the
    'first element in strArray
    strTest = StrReverse(strArray(0))

Posting Permissions

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