Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find() from the right (XP)

    Is there a way of getting the Find() function to start on the right and look left, instead of the standard left to right?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find() from the right (XP)

    No, but depending on what you are trying to do, you might be able to use InStrRev.
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find() from the right (XP)

    I am trying to isolate the last word in a short piece of text.

    Where there are only two words it is easy enough to find the space between, but where there are three or more words (two or more spaces) it is more difficult. Hence my question

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find() from the right (XP)

    Say your text is in cell A1. The following array formula (confirm with Ctrl+Shift+Enter) will return the last word:

    <code>=RIGHT(A1,MATCH(" ",MID(A1,LEN(A1)-ROW(1:255),1),0))</code>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find() from the right (XP)

    Hans gave you a way to do it with a formula. Since you asked about Find, I assume you are trying to do this in VBA. The following will do it in VBA

    <code>
    Dim strWk As String
    strWk = Trim(Range("A1").Value)
    strWk = Right(strWk, Len(strWk) - InStrRev(strWk, " "))
    </code>
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find() from the right (XP)

    Hans and Legare

    Thanks - that solved it

Posting Permissions

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