Overview
Macros such as Hide, Run without a user form, and Run with a user form and parameters make use of regular expressions for pattern matching. Generally, just a simple understanding of regular expressions and a few examples are enough to get by for most use cases. This page has a few simple examples to get started. Use the references for more advanced information. It is recommended to test your regular expressions in one of the well-known regex testing sites such as RegexPlanet or Regex101.
Key tips
- Dot or period (.) is a special regex character. If you really want to match on it, you need to escape it with a backslash: \.
- Don't be confused with generic pattern matching used for file systems for instance. On a file system, *.png means all files ending with .png. That is an illegal regex expression. For regex you need: .*\.png, or to simplify: .*png which finds all files ending in png (not necessarily ending in an extension of PNG).
- Regex is case sensitive by default. In most cases, use the case insensitive flag: (?i). See one of the examples below.
- Use the Dotall mode to match across line breaks.
In dotall mode, the expression . matches any character, including a line terminator. By default this expression does not match line terminators. Dotall mode can also be enabled via the embedded flag expression (?s). (The s is a mnemonic for "single-line" mode, which is what this is called in Perl.)
Simple examples
Advanced examples
Value | Regex | Matches | Find | Demonstrates |
|---|---|---|---|---|
| example.txt |
|  |  | Find string not containing a word. In this example, files that do not have a .png extension |
| example.png |
| | | Find string not containing a word. In this example, files that do not have a .png extension |
| example.jpeg | (?=^((?!\.png).)*$)(?=^((?!\.jpeg).)*$) | | | Find string not containing a word. In this example, files that do not have a .png or .jpeg extension |
| collateral wholesale retail | .*(?=.*\bretail\b.*)(?=.*\bcollateral\b.*).* | | Match exact words anywhere in string. In this case, a blank separated list of labels and both collateral and retail must be included for the match to be successful | |
| wholesale retail | .*(?=.*\bretail\b.*)(?=.*\bcollateral\b.*).* | | Both are required for a match | |
| merger acquisition | .*\b(?:merger|acquisition)\b.* | | Match string containing either word |
References
- RegexOne - interactive tutorials to learn how to construct regular expressions
- Regexlib.com - searchable collection of user-contributed regular expressions
- RegexPlanet test site - test your expressions quickly
- Regex101 test site - understand a regex expression and test it
- Wikipedia regular expressions
- Regular expression reference
- Quick reference - best once you have the concepts
- Using word boundaries
- Regular expression to match string not containing a word?
