Regex: Match pattern, but exclude it if contains specific word [duplicate]

I got some of these lines:

  • qryReservasxMesxAR_Inv 2021 09.xls
  • qryReservasxMesAR_Inv 2021 10.xls
  • qryReservasxMesAR_Inv 2021 11.xls
  • qryReservasxMesSob 2021 12.xls
  • qryReservasxMesxBeneficiarioAR_Inv 2021 07.xls
  • qryReservasxMesxBeneficiarioAR_Inv 2021 08.xls
  • qryReservasxMesxBeneficiarioAR_Inv 2021 09.xls
  • qryReservasxMesxBeneficiarioSob 2021 10.xls
  • qryReservasxMesxBeneficiarioSob 2021 11.xls

I want to match only the ones that contain "mes", "ar" AND "inv". Now, I can't just write that as a pattern because the format is not 100% consistent, so I tried this

Dim RegexUno As Object: Set RegexUno = New RegExp
    With RegexUno
        .Pattern = "mes.*(?!beneficiario).*ar.*inv"
        .Global = False
        .IgnoreCase = True
    End With

My intention is to find any string that has "mes", followed by any character (mostly separation characters, but since you can see they sometimes use "x" to separate words), and then if the word "beneficiario" is found, fail the regex search. After that it would just match AR and INV. However this doesn't seem to work, as I've tried in it regexr.com, so I would like some help with this, thank you.


You want something like this:

mes(?:(?!beneficiario).)*ar.*inv

The problem with your version is that .*(?!beneficiario).* matches any string, followed by any string that doesn't start with beneficiario, so taking one of the examples you have, qryReservasxMesxBeneficiarioAR_Inv 2021 07.xls, here that would match mes, followed by nothing (.*), followed by a string that doesn't start with beneficiario: xBeneficiario, followed by ar.

Instead you want to disallow beneficiario at any character you accept, which I wrote as a negative look-ahead followed by a single character match, repeated as many times as needed. This ensures that any character matched isn't part of a beneficiario prefix at any point.