CODEWITHSUNDEEP
vbaexcel
Wehaga
Wehaga

Reputation: 27

Excel-VBA consecutive dots with regex

I am trying to catch more than 2 consecutive dots in Excel with VBA. I am using the following regex: "(\.{2,})"

It seems to be correct if I try it for example here: https://regex101.com/

However Excel is giving me inconsistent results.

"." returns FALSE -> Correct

".." returns TRUE -> Correct

"…" returns FALSE -> Not correct

"…." returns FALSE -> Not correct

"….." returns TRUE -> Correct

(Just when I copy pasted it here, it seems like Excel is somehow merging 3 dots into 1)

Upvotes: 2

Views: 1054

Answers (2)

Vityata
Vityata

Reputation: 43585

Try like this, it works quite ok for me:

Option Explicit

Public Function TryRegex(s As String) As Boolean

    Dim regEx           As Object
    Dim inputMatches    As Object
    Dim regExString     As String
    Set regEx = CreateObject("VBScript.RegExp")
    With regEx
        .Pattern = "(\.{2,})"
        .IgnoreCase = True
        .Global = True
        Set inputMatches = .Execute(s)
        TryRegex = regEx.test(s)
    End With
End Function

Public Sub TestMe()
    Debug.Print TryRegex(".")                       'False
    Debug.Print TryRegex("..")                      'True
    Debug.Print TryRegex("...")                     'True
    Debug.Print TryRegex("....")                    'True
    Debug.Print TryRegex(".I.live.in.submarine.")   'False
    Debug.Print TryRegex(".a.yellow.one..")         'True
End Sub

The code uses late binding, thus you do not need to reference any additional libraries.

Upvotes: 0

Pat
Pat

Reputation: 2750

Yeah so it looks like Excel by default replaces ... with …. If you want to remove this auto correct you can by going into Options -> Proofing -> AutoCorrect Options (see below) and then just doing a find … replace .... Or you can just include … in your regex like this: (…|\.{2,})

Capture

Upvotes: 1

Related Questions