Excel – VBA – Regex & Replace

microsoft excelvbavbscript

I am very new to VBA. Below is my code to identify & remove only those dots which are appearing after dates and not after the text. But it does not seem to work. 

Sub simpleRegexSearch()

    Dim strPattern As String: strPattern = "[0-9]+[\.]"
    Dim strReplace As String: strReplace = "\."
    Dim myreplace As Long
    Dim strInput As String
    Dim Myrange As Range

    Set regEx = CreateObject("VBScript.RegExp")
    Set Myrange = ActiveSheet.Range("A1")

    For Each cell In Myrange
        If strPattern <> "" Then
            strInput = cell.Value

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            If regEx.TEST(strInput) Then
                 Myrange.Value = (regEx.Replace(strInput, strReplace))

            End If
        End If
    Next

    Set regEx = Nothing

End Sub

Sample two rows of the column I am working on is :-

08-02-18. BM sent email to Matt with IM. 15-02-18. Left voice message for Matt today.
08-02-18. BM sent email with IM. 15-2-18. BM spoke to Adam. He is looking at the IM. 16-2-18. Further discussions with Adam today. Looking to develop an office asset with Childcare.

The desired output is :-

08-02-18 BM sent email to Matt with IM. 15-02-18 Left voice message for Matt today.
08-02-18 BM sent email with IM. 15-2-18 BM spoke to Adam. He is looking at the IM. 16-2-18 Further discussions with Adam today. Looking to develop an office asset with Childcare.

Please help me with the correction in it.

Best Answer

There are a number of changes that could be made to improve your general code. But so far as the regex, to enable it to work as you want, change your pattern and replace strings

Dim strPattern As String: strPattern = "([0-9]+)[\.]"
Dim strReplace As String: strReplace = "$1"

Explanation of Regex and replacement string

([0-9]+)\.

Options: Case insensitive; ^$ match at line breaks

$1

Created with RegexBuddy

Related Question