• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

RegEx (Regular Expressions) in Excel

You are here: Home / Excel VBA / RegEx (Regular Expressions) in Excel
regex regular expressions in excel
February 22, 2019 by Philip Treacy

RegEx stands for regular expression and is a sequence of characters that defines a pattern that can be used to match against strings.

Or, in simpler language, I can create a regex pattern and use it to search a string for that pattern. I can also use the regex pattern to validate input.

A simple pattern looks like this

[a-z]

and I can use that pattern to look for any lower case letter from a to z.

You can use VBA string functions and the LIKE operator, and you can use functions like MID, LEFT, RIGHT, in the worksheet. But none of these match RegEx for the complexity of pattern matching and data validation it provides.

Once you've learned RegEx you can use it with a huge range of programming languages, not just in Excel.

RegEx Patterns

RegEx patterns can be simple, or very complicated, but all are built using the following syntax.

Matching Characters

Pattern Description Example Matches
. Any single character c.t cat, cot, cut, c9t, c@t
[characters] Matches any single character between brackets [ ] [at] Matches only a or t
[^characters] Matches any single character not between brackets [ ] [^at] Matches any single character except a and t
[start–end] Matches any character in range specified between brackets [ ] [0-9] Any single digit between 0 and 9
[a-z] Any single lower case letter
[A-Z] Any single upper case letter
\w Alphanumeric characters and the underscore. The space character is not included in this. s0 _d,f.k s 0 _ d f k
\W Any non-alphanumeric characters or underscore s0 _d,f.k " " , .
\s Any white space character, spaces, tabs etc
\S Any non-white space character
\d Any single decimal digit d 0 and 1 in a0b1
\D Any single non-decimal digit D a and b in a0b1
\ This escapes special characters so you can search for them \. . in s0 _d,f.k
\t Tab (vbTab)
\r Carriage Return (vbCr)
\n New line (vbNewLine)

 

Quantifiers

Quantifiers allow you to modify the basic pattern to specify the number of times you want that pattern to match.

Quantifier Example Description Matches
* ab*c Matches zero or more occurrences of the preceding pattern ac
abc
abbbbbc
+ ab+c Matches one or more occurrences of the preceding pattern abc
abbbbbc
? ab?c Matches zero or one occurrences of the preceding pattern ac
abc
{n} a\d{3} Matches preceding pattern n times Letter a followed by any 3 numeric digits e.g. a000, a123 etc
{n,} a\d{2,} Matches preceding pattern at least n times Letter a followed by at least 2 numeric digits e.g. a00 a123 a00b a1234z
{n,m} a\d{1,3} Matches preceding pattern between n and m times Letter a followed by between 1 and 3 numeric digits e.g. a0 a123 a1234z

 

Capturing

We can specify that we want to capture a pattern, which means not only are we matching a pattern against a string, we're saying save that portion of the string that matches our pattern so we can do something with it.

Up to now we've only described matching a pattern against a string, capturing allows us to extract portions of the string. I'll get to examples of that later, first let's look at the syntax.

Syntax Example Description Matches
(pattern) [a-z](\d+) Matches a single letter followed by one or more numeric digits. The numbers are captured and can be output to the worksheet or otherwise used. If our string is 12b345j2r then we capture 345 and 2

 

Anchors

Anchor Example Description Matches
^ ^Once Matches any string that starts with "Once" Once upon a time
$ time$ Matches any string that ends with "time" Once upon a time

 

VBA RegEx Methods & Properties

To use RegEx in VBA you'll use the RegExp object. To use this you can either set a reference in the VBA editor, or use the CreateObject function.

vba reference to regex library

I prefer to set a reference as it means IntelliSense works for your new object.

intellisense for regex

Properties

The RegExp object has these properties:

  • Pattern - the pattern used for matching against a string
  • IgnoreCase - If True, the matching is case insensitive
  • Global - If True, find all matches of the pattern in the string. Otherwise, just the first match is found
  • MultiLine - If True, patterns are matched across line breaks in multi-line strings

Methods

The methods we can use are:

  • Test - check if the pattern can be found in the string. Returns True if it is found, False if it isn't.
  • Execute - returns occurrences of the pattern matched against the string
  • Replace - replaces occurrences of the pattern in the string

RegEx Functions

We must write our own functions to use the available RegEx methods.

Download the Example Workbook

These functions and lots of sample patterns are available to download.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsm file please ensure your browser doesn't change the file extension on download.

Match

The RegExMatch function is the simplest of the three I have written.

regex match function

You pass in the string to be searched and the pattern. After setting the Pattern property you just call the Test method and return the result, either True or False.

Extract

The RegExExtract function takes 5 arguments:

  • Str - the string to be searched
  • Pattern - the regex pattern
  • MatchIndex - indicates which matched occurrence we want the function to return
  • SubMatchIndex - this is optional. Indicates which SubMatch we want the function to return, more on this below
  • IgnoreCase- this is optional. If True, indicates we want the matching to be case insensitive. Default is True

regex extract function

Matches and SubMatches

When the Execute method is executed, it can return multiple capturing groups, depending on what pattern is used. Let's look at an example.

If our string is She sells sea shells on the sea shore and our pattern is s(..)(..)(..) then we are trying to match the letter s followed by any 2 characters, another 2 characters, and another 2. Note that this includes spaces.

The parenthesis, (..), indicate that we want to capture these characters into separate groups.

When I execute the pattern s(..)(..)(..) against She sells sea shells on the sea shore I'm storing the result in an object I've called Matches.

This object has a property SubMatches where all the other captured groups are stored.

regex match and submatch

To access the matches and submatches we use numbers that I'm calling MatchIndex and SubmatchIndex.

regex match and submatch index

Something a little quirky here is that Matches are indexed from 0, which isn't unusual in programming. But SubMatches are indexed from 1.

So if we look at what matches our pattern

table of regex matches

and I want to access the 2nd submatch of the 3rd match, I would use Matches(2).SubMatches(2) and this would give me th.

If I just want the 1st match he, I'd use Matches(0).SubMatches(0)

Matches(1).SubMatches(0) gives me s - this includes the space before the s.

Matches(3).SubMatches(2) gives me ho.

Remember that our pattern specifies we are looking to match the letter s followed by 3 sets of 2 characters, but we are only capturing the 3 sets of 2 characters. We're not capturing the s

Consuming the String

As each match is made, RegEx works its way along the string looking for the next match.

What's important to understand is that every time a match is made, the portion of the string that matches is 'consumed' and is then not used to look for the next match.

If we look at our string She sells sea shells on the sea shore the first match is She sel so you might think that the next match is sells s but it's not.

regex matches in string

In matching She sel this portion of the string isn't used when looking for the next match. Effectively the next match is looked for in ls sea shells on the sea shore which means the next match is actually s sea s.

Replace

The RegExReplace function takes four arguments:

  • Str - the string to be searched
  • Pattern - the regex pattern
  • ReplaceWith - the string to replace any matches
  • ReplaceAll - this is optional. If True it indicates that you want to replace all matches. Default is False.

regex replace function

Practical Uses for RegEx

Now that you know how to use Regex, let put it to some use.

Data Validation

A very common use is to validate data input/format. You can use the RegExMatch function for this, here are some examples.

Pattern Description Matches
^[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}$ A V4 IP address 1.2.3.4
10.55.55.90
192.168.0.254
^(\+61) ([1-9]) ([0-9]{4}) ([0-9]{4})$ Australian land line including international dialling code +61 7 1111 2222
^3[47][0-9]{13}$ American Express Credit Card. Amex cards begin with either 34 or 37 and have 15 digits 341111111111111
371111111111111

 

Replacing Parts of Strings

If the string in cell A1 is ABC-123-XYZ-45-A-6789 I can use RegExReplace to:

Description Function Call Result
Replace a sequence of numbers with 000 RegExReplace(A1,"\d+","000",TRUE) ABC-000-XYZ-000-A-000
Remove non-alpha characters RegExReplace(A1,"-\d+-*","",TRUE) ABCXYZA
Remove non-numeric RegExReplace(A1,"-*[a-zA-Z]*-+","",TRUE) 123456789

 

Extracting Strings

If the string in cell A1 is IV:INV-32278ABC Widgets PTY LTD I can use RegExExtract to find matches and submatches which enables me to:

Description Function Call Result
Extract the invoice number RegExExtract(A1,"(.*[0-9]+)(.*)",0) IV:INV-32278
Extract the company name RegExExtract(A1,"(.*[0-9]+)(.*)",0,1) ABC Widgets PTY LTD

 

regex regular expressions in excel

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category: Excel VBA
Previous Post:Change PivotTable Aggregation Methods using Excel SlicersChange PivotChart Aggregation Methods using Excel Slicers
Next Post:Scheduling Macros in Excel With Application.OnTimescheduling macros in excel with application ontime

Reader Interactions

Comments

  1. Oz

    February 10, 2022 at 11:18 am

    Wonderful !{3}

    Reply
    • Philip Treacy

      February 10, 2022 at 5:16 pm

      Thanks 🙂

      Reply
  2. ProEdx

    January 30, 2022 at 4:21 pm

    Can anyone please tell me how to regex pattern from this description

    V000306 Mark Roney ; Reversed — Computer Expenses:Computer and software – Recurring ; Reversal Expensify Accrual -Jan-2021 ; Raintank, Inc. ; D2220 US East Coast

    I need to select only one word from this description
    How to create formula

    Reply
    • Catalin Bombea

      February 1, 2022 at 2:13 am

      Hi ProEdx,
      One word, but which one? can be any word from that string, or a specific word?

      Reply
  3. Amy

    September 30, 2021 at 8:10 am

    Could someone tell me how to fix the below syntax error? Thank you!

    =IF(REGEXMATCH(AL2,”(?i)SATA”), “SATA”, IF(REGEXMATCH(AL2,”(?i)NVMe”), “NVMe”, IF(REGEXMATCH(AL2,”(?i)EDSFF”), “NVMe”, IF(REGEXMATCH(AL2,”(?i)SAS”), “SAS”, “–“))))

    Reply
    • Philip Treacy

      October 4, 2021 at 2:26 pm

      Hi Amy,

      The only thing I can see wrong is that the double quotes aren’t correct.

      Your code is using curly quotes like this “

      But VBA requires ‘straight’ double quotes for encasing strings, like this

      "

      So try replacing all the double quotes with straight ones, like this

      =IF(RegexMatch(AL2,"(?i)SATA"), "SATA", IF(RegexMatch(AL2,"(?i)NVMe"), "NVMe", IF(RegexMatch(AL2,"(?i)EDSFF"), "NVMe", IF(RegexMatch(AL2,"(?i)SAS"), "SAS", "–"))))

      Regards

      Phil

      Reply
  4. Jorge Cabral

    August 6, 2021 at 2:44 am

    Hi
    The VBA USERFORM color format is like &H(80 or 00)BBGGRR&, and BBGGRR can be any number or letter, including 8 and 0, putting this, how can i extract the sub-string BBGGRR from that string?

    Thanks

    Reply
    • Catalin Bombea

      August 7, 2021 at 12:38 am

      use the Mid function in vba:
      str=Mid(text,4,6)

      Reply
      • Jorge Cabral

        August 30, 2021 at 11:47 pm

        Hi, with Regular Expressions.

        Reply
        • Catalin Bombea

          August 31, 2021 at 1:26 am

          You can try this pattern in A2:
          (&H00|80)([0-9|A-Z]{1,6})
          Formula:
          =RegExExtract($A$1,$A$2,0,1) , in A1 you should have the Text.

          Reply
  5. Carlos

    May 7, 2020 at 3:50 am

    Hi, is it possible to update the funcions to work in 64 bits Office Packages?

    Reply
    • Catalin Bombea

      May 8, 2020 at 2:25 am

      Hi Carlos,
      Use conditional declarations, this way will wok on both 32-bit and 64-bit platforms:
      #If VBA7 Then
      Declare PtrSafe Sub… (use 64 bit declarations)
      #Else
      Declare Sub… (use 32 bit declarations)
      #EndIf

      Reply
  6. Josef Wagner

    February 7, 2020 at 8:08 pm

    Thank you very much! This helps a lot for using RegEx in Excel. But two things dive me really nuts because I can’t find a solution.

    1. If I look into my add Ins in Excel I do not find your package. I don’t find the functions and properties and I do not know how to transfer them into other Excel documents.
    2. The evaluation seems to be case sensitive but I need it non case sensitive. Due to not seeing the properties of the regex method I can’t change this.

    Currently I’m using Office2013 and there is no VBA installed. Nevertheless your functions work properly, but only in the example workbook.

    L.E.:
    Sorry, now I found it, I needed to activate the developper toos in the menu bar. There I can find and edit the macros and properties.

    Reply
  7. Dang Duy Khanh

    March 29, 2019 at 4:44 pm

    Thanks you so much!

    Reply
    • Philip Treacy

      April 1, 2019 at 2:21 pm

      You’re welcome.

      Reply
  8. David Magnuson

    March 7, 2019 at 5:17 am

    Thanks for an interesting and helpful article.

    In your first table (Matching Characters), I think there is a typo: vbTab should be \t, while \r is vbCR (which isn’t listed).

    Reply
    • Philip Treacy

      May 7, 2019 at 9:47 am

      Thanks David

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.