• Skip to main content
  • Skip to primary sidebar

Ryan McCormick

Dedicated Dad, Software Engineer and Lover of Coffee

Search Text With Regular Expressions in VBA | RegEx VBA

April 30, 2015 by Ryan McCormick 2 Comments

For text items that are difficult to parse, regular expressions can come in handy. For this example, I put together a function that parses email addresses from string text. For simplicity and reference-sake, I adopted an email regex search pattern from: http://www.regular-expressions.info/email.html.

Not all use cases are created equally, re-purpose my example as needed. Also, please comment with questions/comments/improvements.

Regular expression search for email address in VBA

Public Sub filterByRegex()
    ''''''''''''''''''''''''''''''''''''''''''''''
    'BEFORE USE, ADD REFERENCE TO:
    'Microsoft VBScript Regular Expressions 5.5
    ''''''''''''''''''''''''''''''''''''''''''''''
    Dim RegEx As New RegExp
    Dim mCol As MatchCollection
    Dim mItem As Variant
    Dim iInput As String
    
    iInput = "This is a test sampleemail@example.com to " _
    & "see if the email test@example.com addresses can be found"
    
    With RegEx
        'pattern adopted from practical implementation of RFC 5322:
        'http://www.regular-expressions.info/email.html
        .Pattern = "[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@" _
        & "(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?"
        
        ''''''''''''''
        ' Global:
        ' True = Match All occurrences in search string
        ' False = Match only the first occurrence in search string
        ''''''''''''''
        .Global = True
    End With

    Set mCol = RegEx.Execute(iInput)
    
    For Each mItem In mCol
        'Item output here, debug.print for demo purposes
        Debug.Print mItem
    Next mItem
    
    Set RegEx = Nothing
    Set mCol = Nothing

End Sub

Related

Filed Under: Microsoft Access, VBA Tagged With: email address regex, regex, regular expressions, vba

Reader Interactions

Comments

  1. Jim says

    October 13, 2017 at 3:44 pm

    absolutely brilliant! This solved my problem and made my day!

    Reply
  2. AzDayton says

    May 22, 2018 at 12:51 pm

    Succinct. Just the way I like my code examples! 🙂
    Took me almost no time at all to adapt to my own needs.
    Thank you!

    Reply

Leave a Reply Cancel reply

Primary Sidebar

Recent Posts

  • Force Quit Kill all Chrome Windows MacOS
  • SOLVED: Angular 6 CLI Karma Stuck in Single Run | Karma Stops Running
  • How to Manually Install Java 8 on Ubuntu 18.04 LTS
  • Remove VirtualBox from Ubuntu 16.04 Xenial
  • Clear all Node Modules Folders Recursively Mac/Linux

Recent Comments

  • KKV on Webstorm adding spaces between imports and braces | JavaScript and TypeScript
  • jusopi on Clear all Node Modules Folders Recursively Mac/Linux
  • Qaisar Irfan on Clear all Node Modules Folders Recursively Mac/Linux
  • mustafa on Remove VirtualBox from Ubuntu 16.04 Xenial
  • Pourya on How to Manually Install Java 8 on Ubuntu 18.04 LTS

Archives

  • May 2019
  • May 2018
  • April 2018
  • March 2018
  • January 2018
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • March 2017
  • December 2015
  • November 2015
  • July 2015
  • April 2015
  • February 2015
  • September 2014
  • June 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014
  • October 2013
  • August 2013
  • June 2013
  • April 2013
  • March 2013
  • February 2013
  • December 2012
  • October 2012
  • September 2012
  • August 2012
  • July 2012
  • May 2012
  • March 2012
  • February 2012
  • December 2011
  • November 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • August 2009
  • July 2009
  • May 2009

Categories

  • Angular
  • Angular 2
  • AngularJS (1x branch)
  • Computer Q&A
  • ES2015
  • Internet Marketing
  • Javascript
  • Job Interviews
  • Job Search
  • Karma
  • Laravel
  • Linux
  • Linux/Unix Tips
  • MacOS
  • Microsoft Access
  • Microsoft Excel
  • Microsoft Outlook
  • Microsoft Word
  • News
  • Node
  • Open Source
  • PHP
  • Protractor
  • Resume Writing
  • Spring Boot
  • SQL
  • Ubuntu
  • VBA
  • VBScript
  • VirtualBox
  • Web Development
  • Windows Tips
  • Wordpress

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Copyright © 2023 · Magazine Pro on Genesis Framework · WordPress · Log in