How to delete a specific Number of Strings after RegEX in VBA?

Question

i'am new to VBA and i am trying to lookup a function that deletes for each Cell in a Column the next two Strings after an Regular Expression.

Below the RegEx

[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1]) (2[0-3]|[01][0-9]):[0-5][0-9]

Here is some of the code I wrote

Private Sub DeleteRegexPattern()
    Dim xRg As Range
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = Application.InputBox("Select a range:", "PivData Hider", xTxt, , , , , 8)
    Set Myrange = Application.Intersect(Myrange, Myrange.Worksheet.UsedRange)
    If Myrange Is Nothing Then Exit Sub



    For Each C In Myrange
        KIDPattern = ("([A-Z]{1})([0-9]{5})| |(.*)@.*")
        emailPattern = "(.*)@.*"

        If KIDPattern <> "" Then
            strInput = C.Value

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = KIDPattern

            End With


            If regEx.Test(strInput) Then
              C.Value = regEx.Replace(strInput, "")

            End If
        End If
    Next
End Sub

Example Input


    2017-02-14 19:30 John Smith

Output

2017-02-14 19:30

John Smith is removed or replaced with ""

I want to delete John Smith but dont know how to. Any Ideas?

Check out [How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) try to accomplish something on your own and come back with your code and a proper example data (see [mcve]). — Pᴇʜ, Jun 03 '20 at 13:10
Note that your second `With regEx` block makes the first useless because it overwrites it completely. You can only use one pattern at a time. Also note that none of your patterns in your code match `2017-02-14 19:30 John Smith` at all. You need a pattern that matches the data part you want to keep in that cell. So if you want to keep `2017-02-14 19:30` then you need a pattern that matches this data and re-write the match to that cell. For example [`[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}`](https://regex101.com/r/cy7ON7/1) will match that and you just need to write that match to the cell. — Pᴇʜ, Jun 05 '20 at 06:15
I fixed the With RegEx . The pattern doesn’t match because it is not in the code yet . I want to match the whole 2017-02- 14 19:30 John Smith and not only the date and Time. I can’t find a way to look for the next 2 Strings coming after time and delete them . That is where i need help — , Jun 05 '20 at 07:32
Then please [edit] your question and show the updated code. And if you want to turn `2017-02-14 19:30 John Smith` into `2017-02-14 19:30` you just need to match the date and time and write that match into the cell. That's all. Writing `2017-02-14 19:30` **is** actually the same as deleting `John Smith`. • If that is not what you want, you need to give a better example in your question. — Pᴇʜ, Jun 05 '20 at 07:55

score 0 · Answer 1 · answered Jun 05 '20 at 08:30

I am not familiar with the specialties of VBA regexes. However, if you want to match the date and time string of a string that contains an additional first and last name, a proper regex for that might be

^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}) \w+ \w+$

As you can see, I put the date and time matches into parentheses, which makes them a group. If you match a string to this and then select the (first and only) group from the parsed regex, you'll get your date and time.

You can also test strings against regexes by using an online service e.g. https://regex101.com/

(\w+ \w+) is what i needed. Thanks for the Tipp Richard – Jun 05 '20 at 15:11 — , Jun 05 '20 at 15:11

How to delete a specific Number of Strings after RegEX in VBA?

1 Answers1