VBA - Regex & Replace

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.

2 Answers

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

• Match the regex below and capture its match into backreference number 1 ([0-9]+)
  • Match a single character in the range between “0” and “9” [0-9]+
    • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +
• Match the character “.” literally \.

$1

• Insert the text that was last matched by capturing group number 1 $1

Created with RegexBuddy

[] is a range of characters. So strpattern = “([0-9]+).” and strReplace = “$1”..