Regular Expressions in VBA

Re: Regular Expressions in VBA

I have never heard of Regex. What is it and what does it do?

Alastair

Re: Regular Expressions in VBA

Hard to tell without more information, but check out the VBA help on the "Like" operator.

Alastair

Re: Regular Expressions in VBA

Hi,

Try looking into the VBA Help file for Wildcards.

Specifically "Wildcard Characters Used in String Comparisons"

This seems along a similar vien.

John

Re: Regular Expressions in VBA

Hi c,

Sure, you can use Regular Expressions. The following UDF extracts the 6 numbers in the given string:

Sub Test()
    Const strTest As String = "qwerty123456uiops"
    MsgBox RE6(strTest)
End Sub


Function RE6(strData As String) As String
    Dim RE As Object, REMatches As Object


    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = "[0-9][0-9][0-9][0-9][0-9][0-9]"
    End With
    
    Set REMatches = RE.Execute(strData)
    RE6 = REMatches(0)


End Function

You may also be interested in some of the information at Tushar Mehta's page.

HTH

Re: Regular Expressions in VBA

Well if your pattern is "5-hepten-2-one" it will evaluate true. So that's an easy one, it gets more advanced if you want it be at a specific location.
"5-hepten-2-one$" Will also eval true, the pattern states that it has to be the end of the string.
"([\w|-]*)5-hepten-2-one$" Will eval true on "6-methyl-5-hepten-2-one", but also set "6-methyl-" as first match in case you want to extract it.
"^([\w|-]*)5-hepten-2-one$" Will do the same as the line above, but it requires that there is no space or similar at the start of the text.

You can go here for more syntax http://www.aivosto.com/regexpr/help/syntax.html

Re: Regular Expressions in VBA

Sub test()
Dim txt As String
txt = "6-methyl-5-hepten-2-one"
With CreateObject("VBScript.RegExp")
    .Pattern = "\d+-\D+-\d+-\D+(?!.)"
    If .test(txt) Then
        MsgBox .Execute(txt)(0)
    End If
End With
End Sub

Use of Replace method

Sub test2()
    Dim txt As String
    txt = "6-methyl-5-hepten-2-one"
    With CreateObject("VBScript.RegExp")
        .Pattern = ".*((\d+-\D+){2})(?!.)"
        If .test(txt) Then
            MsgBox .Replace(txt, "$1")
        End If
    End With
End Sub

Re: Regular Expressions in VBA

Quote from cramirez06;187758

Thanks Richie, that site really helped. I have another question though: I have only just started learning regex so it might be a stupid one, but... Do you know how I could turn a string into a regular expression pattern? More exactly, if I have a string
"6-methyl-5-hepten-2-one", how can I turn it into a regex withing VBA code to search just for "5-hepten-2-one" or something similar? Because I will have to do so for many such names.

You're probably looking for something like:

"^[0-9]-methyl-[0-9]-hepten-[0-9]-one$" to match your string but with any single number instead of 6, 5, and 2 specifically like 8-methyl-2-hepten-1-one. You can also use ^ and $ to match the beginning and end of the line. The "-" has to be included between each element.

"[0-9]+-methyl-[0-9]+-hepten-[0-9]+-one" is the same as above but with one or more numbers instead of one like 889-methyl-4-hepten-1235347686572465235758573467-one

"[0-9]+-[A-Za-z]+-[0-9]+-[A-Za-z]+-[0-9]+-[a-z]+" is the same as above but the words can be any combination of upper and lower case letters, except the last is only lower case like 2235-WhatEVEr-98-mOrEWORdswithOUTSPaces-098-onlylowercasehere

"[0-9]*[02468]-[A-Za-z]+-[0-9]{3,5}-[A-Za-z]+-[0-9]{3,}-(one|two|five|nine)" matches even numbers ([0-9]* matches 0 or more but must end with even number), a word, 3-5 numbers, another word, at least 3 numbers, and then one of the words one, two, five, or nine. () groups things together and | means 'or'.

"([0-9]+-[A-Za-z]{3,10}-?)+" matches any number, then any word 3-10 characters long, but then repeats that as many times as it can. It matches 8-hepten and even 945-hepten-9867-oxy-8976-one-987-eight-987-morewords-9842-methyl... The "?" matches 0 or 1, so the "-" won't be at the end of the last matched word. If you know it will always be a group of 6 (3 number-word combos), then you can replace the + with {3}.

These might not be exact, but they should give an idea of how specific or general they can define your strings.

Regular expressions make matches more precise and much more efficient to program than parsing text and using a couple wildcards. I didn't learn much about them until I did analysis outside of Excel, but I wish I had learned them earlier. This is a small contribution to introduce it to others that wouldn't come across it otherwise because it should be a more prominent tool in VBA. I start with a few strings I want to match, and some I don't want to match, and replace each part with a more general match, similar to shown above. Test it and make sure it works like expected.

Re: Regular Expressions in VBA

Sub test2() Dim txt As String txt = "6-methyl-5-hepten-2-one" With CreateObject("VBScript.RegExp") .Pattern = ".*((\d+-\D+){2})(?!.)" If .test(txt) Then MsgBox .Replace(txt, "$1") End If End With End Sub code is awesome jindon , i want to ask to you how if txt applicated in cell how if i want to find that txt in cell and replace it with your code. can you help me to give me example with your code to find replace string in cell thanks jindon

Re: Regular Expressions in VBA

Please do not post your question in a thread started by another member.

Start your own thread and make sure you give it a search friendly title that aptly describes your issue. This is one of the Rules you agreed to when you joined the board.

If you think this thread can help describe your issue, or you want to expand on the answers already given, you can include a link by copying the URL from the address bar of your browser and pasting into your new message.

Re: Regular Expressions in VBA

yudisulistiyo

You should open your own thread. (Forum rule)
Link to this thread if you need to.

Re: Regular Expressions in VBA

ok sorry all i dont know for the future i ll create my thread in ozgrid thanks before