vba help - Read text value using Regular expression

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
957
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,719
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.

VBA Code:
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...


Hope this helps!
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
957
Office Version
  1. 2010
Platform
  1. Windows
Hi Domenic.

Thanks for your help it worked. 🕺 (y)
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,719
Thanks for your help it worked. 🕺 (y)

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

Well-known Member
Joined
Feb 4, 2009
Messages
957
Office Version
  1. 2010
Platform
  1. Windows
Hi Domenic,

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


Thanks
mg
 

Forum statistics

Threads
1,141,002
Messages
5,703,663
Members
421,310
Latest member
CindaH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top