Using Regular expression in VBA

Question

This is my sample record in a Text format with comma delimited

901,BLL,,,BQ,ARCTICA,,,,

i need to replace ,,, to ,,

The Regular expression that i tried

  With regex
    .MultiLine = False
    .Global = True
    .IgnoreCase = False
    .Pattern="^(?=[A-Z]{3})\\,{3,}",",,"))$ -- error

Now i want to pass Line from file to Regex to correct the record, can some body guide me to fix this i am very new to VBA

I want to read the file line by line pass it to Regex

score 2 · Answer 1 · answered Feb 17 '14 at 00:40

Looking at your original pattern I tried using .Pattern = "^\d{3},\D{3},,," which works on the sample record as with the 3 number characters , 3 letters,,,

In the answer I have used a more generalised pattern .Pattern = "^\w*,\w*,\w*,," This also works on the sample and mathces 3 commas each preceded with 0 or more alphanumeric characters followed directly by a fourth comma. Both patterns require a match to be from the begining of the string.

Pattern .Pattern = "^\d+,[a-zA-Z]+,\w*,," also works on the sample record. It would specify that before the first comma there should be 1 or greater numeric characters (and only numeric characters) and before the second comma ther should be 1 or more letters (and only letters). Before the 3rd comma there could be 0 or more alphanumeric characters.

The left function removes the rightmost character in the match ie. the last comma to generate the string used by the Regex.Replace.

Sub Test()
    Dim str As String

    str = "901,BLL,,,BQ,ARCTICA,,,,"
    Debug.Print
    Debug.Print str

    str = strConv(str)
    Debug.Print str
End Sub

Function strConv(ByVal str As String) As String

    Dim objRegEx As Object
    Dim oMatches As Object
    Dim oMatch As Object

    Set objRegEx = CreateObject("VBScript.RegExp")
    With objRegEx
        .MultiLine = False
        .IgnoreCase = False
        .Global = True
        .Pattern = "^\w*,\w*,\w*,,"
    End With

    Set oMatches = objRegEx.Execute(str)
    If oMatches.Count > 0 Then
        For Each oMatch In oMatches
            str = objRegEx.Replace(str, Left(oMatch.Value, oMatch.Length - 1))
        Next oMatch
    End If
    strConv = str

End Function

score 1 · Answer 2 · answered Feb 15 '14 at 07:30

1

Try this

Sub test()

    Dim str As String
    str = "901,BLL,,,BQ,ARCTICA,,,,"

    str = strConv(str)
    MsgBox str

End Sub

Function strConv(ByVal str As String) As String

    Dim objRegEx As Object, allMatches As Object
    Set objRegEx = CreateObject("VBScript.RegExp")

    With objRegEx
        .MultiLine = False
        .IgnoreCase = False
        .Global = True
        .Pattern = ",,,"
    End With

    strConv = objRegEx.Replace(str, ",,")

End Function

answered Feb 15 '14 at 07:30

Santosh

12,175
4
41
72

thanks! and how do i pass the entire file ? to read line by line! – vhadalgi Feb 15 '14 at 08:03
@VijaykumarHadalgi See this [link](http://stackoverflow.com/questions/11528694/read-parse-text-file-line-by-line-in-vba) which shows how to read text file line by line. – Santosh Feb 15 '14 at 08:09
and the regex replace even last 3 commas with 2 commas,it must only replace after `BLL` – vhadalgi Feb 15 '14 at 09:37

Using Regular expression in VBA

2 Answers2