vba help - Read text value using Regular expression

[Mallesh23]

Hi Team,

I have below data from text file. I want to extract only few text line to dictionary.
Text starts with {ABC,XYZ,PQ,OP,ST} Using regular expresion.

Can you help in creating function of Regular expression.

str = joins(array{"ABC","XYZ","PQ","OP","ST"}
exp - if instr(1,str,abc,1)>0 then
dict.add txt.line

"ABC 564564.0000 454.000 456.00"
"PQR 564564.0000 454.000 456.00"
"DEF 564564.0000 454.000 456.00"
"XYZ 564564.0000 454.000 456.00"
"PQ 564564.0000 454.000 456.00"
"JKL 564564.0000 454.000 456.00"
"OP 564564.0000 454.000 456.00"
"ST 564564.0000 454.000 456.00"


Thanks
mg

 

[Domenic]

The following example uses a function to return True when the line of text starts with ABC, XYZ, PQ, OP, or ST. Otherwise, it returns false.

Option Explicit

Sub test()

    If TextLineRegExpValidation("PQ 564564.0000 454.000 456.00", "^(ABC|PQR|DEF|XYZ|PQ|JKL|OP|ST)\s") Then
        'add to dictionary
        '
        '
    End If
   
End Sub


Function TextLineRegExpValidation(ByRef textLine As String, ByRef regexPattern As String) As Boolean

    Dim RegExp As Object
    Set RegExp = CreateObject("VBScript.RegExp")
   
    With RegExp
        .Pattern = regexPattern
        .Global = True
        .IgnoreCase = True
        .MultiLine = True
    End With
   
    TextLineRegExpValidation = RegExp.test(textLine)
   
    Set RegExp = Nothing

End Function

See the following link...

regex101: build, test, and debug regex

Regular expression tester with syntax highlighting, explanation, cheat sheet for PHP/PCRE, Python, GO, JavaScript, Java, C#/.NET, Rust.

regex101.com

Hope this helps!

 

[Mallesh23]

Hi Domenic.

Thanks for your help it worked. ?
What is \s. and ^ in regular expression


One more small help. if you can using regular expression.

Read all text line into dictionary. Below the Period Data
Include Total also.

Logic Read each text line where total alphabets [small and Capital ] in a line should be less than 6.


===Below is a Data in Text File===
Blah BlahBlahBlah 2021June Page Company
Blaha bhla Blah
Period amt1 amt2 amt3 amt4
1Mo -456.00 56456.00 879.00- 465.00
1WK -456.00 56456.00 879.00- 465.00
18M -456.00 56456.00 879.00- 465.00
30M 12300.00-
7y 6m 5646.00 -5665
10y
=========================================
Total 1231 1232 12313 12331

Blah Blah Blah
Blah BlahBlahBlahBlahBlahBlahBlahBlah

Period amt1 amt2 amt3 amt4
1Mo -456.00 56456.00 879.00- 465.00
1WK -456.00 56456.00 879.00- 465.00
18M -456.00 56456.00 879.00- 465.00
30M 12300.00-
7y 6m 5646.00 -5665
Blah BlahBlahBlahBlahBlahBlahBlahBlah


Thanks
mg

 

[Domenic]

Thanks for your help it worked. ?

You're very welcome!

What is \s. and ^ in regular expression

^ - matches the beginning of the line
\s - matches spaces, tabs, and line breaks

If you check out that link I posted, and you move your cursor over each element of the regular expression, you'll see that a description of their meaning will be provided for you. It's a great tool to learn regular expressions.

 

[Mallesh23]

Hi Domenic,

Thanks for help, it worked, slowing learning Regular expression.


Thanks
mg

 

[Domenic]

That's great, you're very welcome, cheers!