Announcement

Collapse
No announcement yet.

Regular Expressions in VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Regular Expressions in VBA



    Does anybody know a way to incorporate Regex in VBA code?

  • #2
    Re: Regular Expressions in VBA

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

    Alastair
    Einstein:
    Things should be made as simple as possible, but not any simpler

    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

    Comment


    • #3
      Re: Regular Expressions in VBA

      Oh sorry, by regex I meant Regular Expressions

      http://www.regular-expressions.info

      Comment


      • #4
        Re: Regular Expressions in VBA

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

        Alastair
        Einstein:
        Things should be made as simple as possible, but not any simpler

        Be sure to check out TemplateZone for all your Microsoft Office Needs.
        Get OfficeReady Professional 3.0 here!

        Comment


        • #5
          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

          Comment


          • #6
            Re: Regular Expressions in VBA

            Hi c,

            Sure, you can use Regular Expressions. The following UDF extracts the 6 numbers in the given string:
            Code:
            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
            Cross-poster? Read this: Cross-posters
            Struggling to use tags (including Code tags)? : Forum tags

            Comment


            • #7
              Re: Regular Expressions in VBA

              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.

              Comment


              • #8
                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

                Comment


                • #9
                  Re: Regular Expressions in VBA

                  Code:
                  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
                  Code:
                  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
                  Last edited by jindon; May 10th, 2012, 18:09. Reason: Added more code

                  Comment


                  • #10
                    Re: Regular Expressions in VBA

                    Originally posted by cramirez06 View Post
                    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.

                    Comment


                    • #11
                      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

                      Comment


                      • #12
                        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.

                        Comment


                        • #13
                          Re: Regular Expressions in VBA

                          yudisulistiyo

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

                          Comment


                          • #14


                            Re: Regular Expressions in VBA

                            ok sorry all i dont know for the future i ll create my thread in ozgrid thanks before
                            Last edited by cytop; July 17th, 2012, 18:41. Reason: Removed quoted posts.

                            Comment

                            Working...
                            X