There is a well-known joke in the IT world – if you have a problem and you try RegEx to solve it, now you have two problems.
Still, RegEx in VBA (and thus in Excel) exists as an additional library. If you are not a fan of asking people to add libraries to your project, in VBA you may use the late binding. This is a simple example of a custom excel formula for verification of an email through RegEx:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Option Explicit Function RegExample(inputString As String, _ Optional globalRe As Boolean = True, _ Optional ignoreCase As Boolean = True) As Boolean Dim re As Object Set re = CreateObject("vbscript.regexp") With re .Global = globalRe .Pattern = "^[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})$" .ignoreCase = ignoreCase RegExample = .test(inputString) End With End Function |
And this is how it looks in Excel: