The Run with a user form and parameters macro makes use of regular expressions for pattern matching. Generally, just a simple 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.
- 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.)
|ABC||A.C||. matches any single character|
* indicates 0 or more characters
|A.B||A\.B||Escape special regex characters with backslash if necessary|
Regex is NOT generic matching
|ABC||A.+||+ indicates 1 or more|
|ABC||[ABCD]||[ ] indicates a class of characters|
- indicates a range of characters
|ABC||[AB^C]||^ in a class means NOT the following character|
|ABC||DEF|ABC||| indicates OR|
|image.JPG||.*png|.*jpg||defaults to case sensitive matching|
(?i) indicates case insensitive matching
|ABAB||(AB)+||() indicates a grouping|
|112233||\d+||\d for digits|
|A B||A\s*B||\s for whitespace|
|ABC||\S*||\S for non whitespace|
|\S+||Value must have at least 1 non whitespace character|
|XYZ,ABC,UVW||.*\bABC\b.*||Word boundaries. Finding words in a comma or blank separated list using word boundaries|
|ABC||(?m)(^ABC$)|(^ABC,)|(,ABC,)|(,ABC$)||Looking for text matches in a comma separated list by covering all cases: only, start, middle, and end. This uses the multi-line flag: (?m)|