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

Question

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.

Can you explain some more about that "beneficiario" part? What do you mean ignore that string? You used `(?!` in your regex, which is a negative lookahead, ie fail the regex if encountered, is that your intent? — Blindy, Jan 20 '22 at 17:12
Yes, I want to ignore the ones that have "beneficiario" in it. First I tried just using "mes.*ar.*inv", but that would also match the ones that include "beneficiario" which I don't need. — Irving Pérez, Jan 20 '22 at 17:14

score 2 · Accepted Answer · answered Jan 20 '22 at 17:24

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.

Works just fine, many thanks – Irving Pérez Jan 20 '22 at 17:34 — Irving Pérez, Jan 20 '22 at 17:34

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

1 Answers1