locked
InStrRev() bug in Excel 2016 VBA RRS feed

  • Question

  • Try this in the immediate window:

    ? InStrRev("ab","b",2)

    This gives the expected answer, 2. Now try this:

    ? InStrRev("abc","bc",2)

    This yields 0. For some reason, matching the substring at the very end only works for a single character search argument.

    ? InStrRev("abcbc","bc",4) will yield 2, i.e. the second occurrence of "bc" is missed.

    Thanks

    Tom

    Wednesday, May 6, 2020 12:14 AM

All replies

  • Hi,

    There is no error or but in the below.
       
    I'm afraid you misunderstand the syntax of InStrRev.
    Please refer to this article MS Excel: How to use the INSTRREV Function (VBA).

    Regards,

    Ashidacchi -- http://hokusosha.com

    Wednesday, May 6, 2020 6:14 AM
  • Perhaps this will help:

    In InStrRev("abc","bc",2), we start looking from the second position ("b") backwards. The entire string "bc" must end at or before position 2. There is no such occurrence, hence the function returns 0.

    In InStrRev("abcbc","bc",4), we start looking from the fourth position (the second "b") backwards. The entire string "bc" must end at or before position 4. The second occurrence doesn't satisfy this condition: it ends at position 5. So the first occurrence, ending at position 3, is found. The function returns its start position 2.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Wednesday, May 6, 2020 1:37 PM