RegEx (Regular Expressions) in Excel • My Online Training Hub | excel substring pattern match
RegExstandsforregularexpression[1]andisasequenceofcharactersthatdefinesapatternthatcanbeusedtomatchagainststrings.Or,insimplerlanguage,Icancreatearegexpatternanduseittosearchastringforthatpattern.Icanalsousetheregexpatterntovalidateinput.Asimplepatternlookslikethis[a-z]andIcanusethatpatterntolookforanylowercaseletterfromatoz.YoucanuseVBAstringfunctions[2]andtheLIKEoperator[3],andyoucanusefunctionslikeMID[4],LEFT,RIGHT,intheworksheet.ButnoneofthesematchRegExforthecomplexityofpatternmatchingan...
RegEx stands for regular expression[1] 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[2] and the LIKE operator[3], and you can use functions like MID[4], LEFT, RIGHT, in the worksheet. But none of these match RegEx for the complexity of pattern matching and data validation it provides.
Once youve learned RegEx you can use it with a huge range of programming languages, not just in Excel.
RegEx PatternsRegEx patterns can be simple, or very complicated, but all are built using the following syntax.
Matching Characters Pattern Description Example Matches...