Replace all text in text file using regular expression

Question

I have a text file with following text

161624.406 : Send:[sometext1]
161624.437 : Send:[sometext2]
161624.468 : Send:[sometext3]
161624.499 : Send:[sometext4]
161624.531 : Send:[sometext5]

I want to keep only the sometext part in that file. Desired output is

sometext1
sometext2
sometext3
sometext4
sometext5

I am using the following code in Excel-VBA

Public Sub testa()
    a = "C:\Users\pankaj.jaju\Desktop\test.log"

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTxtFile = objFSO.OpenTextFile(a, 1)
    strText = objTxtFile.ReadAll
    objTxtFile.Close
    Set objTxtFile = Nothing


    Set objRegEx = CreateObject("VBScript.RegExp")
    With objRegEx
        .Global = True
        .MultiLine = True
        .Pattern = "\[([^]]+)\]"
        Set objRegMC = .Execute(strText)
        b = objRegMC(0).SubMatches(0)
    End With
    Set objRegEx = Nothing

    Debug.Print b
End Sub

The problem is the output is displayed as sometext1 only. How do I ReplaceAll in the text file and save the file with the desired text only.

Answer 1

The regex.Replace method should do the trick.

Separate your pattern into groups like this: "(.*?)(\[)([^]]+)(\])(.*?)"

And now you can replace your input string with the matching group which is group three in this case: objRegEx.Replace(strText, "$3")

---

Here is a helpful link to different examples of Regex within Excel.