Lookbehind on regex for VBA?

by

VBA offers lookahead (both positive and negative) but rather inconsistently not lookbehind.

The best example of using Regex with VBA that I have seen is this article by Patrick Matthews.

[Updated example using Execute rather than Replace]

While I am not completely clear on your usage you could use a function like this:

  • It skips any words starting with a or A (case-insensitivity).
  • For all words not starting with a/A it returns everything from the second character on. This is achieved by using a submatch (the pattern inside ( and ) is the first submatch).

To understand the complete pattern, you may refer to the explanation on regex101.

    Sub TestString()
       MsgBox ReducedText("cfat dcat")
       ' results in:        fat  cat
       MsgBox ReducedText("Sat all over the hat again")
       ' results in:        at      ver  he  at
    End Sub

    Function ReducedText(strIn As String) As String
       Dim objRegex As Object
       Dim objRegMC As Object
       Dim objRegM As Object
       Dim strOut As String
       Set objRegex = CreateObject("vbscript.regexp")
       With objRegex
          .IgnoreCase = True
          'not needed if matching the whole string
          .Global = True
          .Pattern = "\b[^a\s]([a-z]+)"
          If .test(strIn) Then
             Set objRegMC = .Execute(strIn)
             For Each objRegM In objRegMC
                strOut = strOut & objRegM.submatches(0) & vbNewLine
             Next
             ReducedText = strOut
          Else
             ReducedText = "Starts with A"
          End If
       End With
    End Function

More Related Contents:

  1. How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
  2. How does the regular expression ‘(?
  3. Regular Expression Lookbehind doesn’t work with quantifiers (‘+’ or ‘*’)
  4. Returning a regex match in VBA (excel)
  5. What’s the technical reason for “lookbehind assertion MUST be fixed length” in regex?
  6. How to Use/Enable (RegExp object) Regular Expression using VBA (MACRO) in word
  7. Does lookbehind work in sed?
  8. Splitting String in VBA using RegEx
  9. How do I get regex support in Excel via a function, or custom function?
  10. RegEx with Excel VBA on Mac
  11. Does lookaround affect which languages can be matched by regular expressions?
  12. How to match the first word after an expression with regex?
  13. Awk – replace coumn 2 in table 1 from coumn 2 in table 2 based on matching data in column 1 (common between tables)
  14. How do I match any character across multiple lines in a regular expression?
  15. How do you match only valid roman numerals with a regular expression?
  16. Validating IPv4 addresses with regexp
  17. Regular expression pipe confusion
  18. Find CRLF in Notepad++
  19. Jmeter extracting fields/parsing JSON response
  20. How to back reference “inner” selections ( () ) in a regular expression?
  21. Regular expression – PCRE does not support \L, \l, \N, \P,
  22. mod_rewrite: replace underscores with dashes
  23. Multiline regex to match config block
  24. Remove ‘index.php’ from URL with .htaccess
  25. Regex Until But Not Including
  26. Match at every second occurrence
  27. regular expression capture nth match
  28. What regex will match every character except comma ‘,’ or semi-colon ‘;’?
  29. Regex that matches integers in between whitespace or start/end of string only
  30. Difference between regex quantifiers plus and star

Leave a Comment