Matches expression X if it is NOT followed by Y Matches expression X when it is followed by Y (i.e. In VBA RegEx flavor, lookbehinds are not supported. These expressions are sometimes called "zero-width assertions" or "zero-width match" because they match a position rather than actual characters. Lookaround constructs are helpful when you want to match something that is or isn't followed or preceded by something else. In she sells sea-shells, matches sells and shells Matches any single element separated by the vertical bar The alternation operand enables the OR logic, so you can match either this or that element. Matches joy in enjoyable, but not as a separate word. Matches joy as a separate word, but not in enjoyable. AnchorĪny number of digits at the start of the string.Īny number of digits at the end of the string. Non-capturing group: matches a group but does not capture itĪnchors specify a position in the input string where to look for a match. In 5 cats and 10 dogs, captures 5 (group 1) and 10 (group 2) SyntaxĬapturing group: captures a matching substring and assigns it an ordinal number Grouping constructs are used to capture a substring from the source string, so you can perform some operation with it. Matches the preceding pattern between n and m times Matches the preceding pattern n or more times Zero or one occurrence, but as fewer as possible One or more occurrences, but as fewer as possible Zero or more occurrences, but as fewer as possible A quantifier always applies to the character before it. Quantifiers are special expressions that specify the number of characters to match. Matches any character in the range between the brackets Matches any single character NOT in the brackets
Matches any single character in the brackets Using these patterns, you can match elements of different character sets.
Word character: any ASCII letter, digit or underscoreĪny character that is NOT an alphanumeric character or underscoreĮscapes special meaning of a character, so you can search for itĮscapes a period so you can find the literal "." character in a string Whitespace character: space, tab, new line and carriage return Wildcard character: matches any single character except a line breakĭot, hot, pot, character: any single digit from 0 to 9 These are the most frequently used patterns to match certain characters. If you are comfortable with regular expressions, you can jump straight to the RegExp functions. It may also work as your cheat sheet when studying further examples.
For this, there are plenty of resources online, from free tutorials for beginners to premium courses for advanced users.īelow we provide a quick reference to the main RegEx patterns that will help you get a grasp of the basics.
This tutorial does not aim to teach you regular expressions. Whether a regex pattern is very simple or extremely sophisticated, it is built using the common syntax.
NET based) or install third-party tools supporting regexes. To be able to use regular expressions in your formulas, you'll have to create your own user-defined function (VBA or. Regrettably, there are no inbuilt Regex functions in Excel. The latter has a special RegExp object, which we'll utilize to create our custom functions. Regular expressions are used in many programming languages including JavaScript and VBA. For example, matches any single digit from 0 to 5.
Regular expressions have their own syntax consisting of special characters, operators, and constructs. If you are familiar with a wildcard notation, you can think of regexes as an advanced version of wildcards. Using that pattern, you can find a matching character combination in a string or validate data input.