Regular expressions in Excel

[vigi98]

Hello all,

I would like to know if it is possible to handle regular expressions in
Excel, in VB or in formulas. I would like to do things such as "if
cells(x,y)=<regexp> then ..."

Is that possible ?

Thanks in advance.

 

[Bob Phillips]

You can use Regular Expressions if you use VBScript.RegExp, creating a
function that you can use.

Here is an example that validates email addresses.


'-----------------------------------------------------------------
Public Function ValidEmail(Adress As String) As Boolean
'-----------------------------------------------------------------
Dim oRegEx As Object
Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
' .Pattern = "^(\w+\.)*(\w+)@(\w+\.)+([a-zA-Z]{2,4})$"
ValidEmail = .Test(Adress)
End With
Set oRegEx = Nothing
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ron Rosenfeld]

Hello all,

I would like to know if it is possible to handle regular expressions in
Excel, in VB or in formulas. I would like to do things such as "if
cells(x,y)=<regexp> then ..."

Is that possible ?

Thanks in advance.

In addition to what Bob wrote about using regular expressions in VBA, you can
also download and install Longre's free morefunc.xll add-in which enables the
use of regular expression in worksheet functions. See http://xcell05.free.fr/
but be aware there is a small bug in the routine that may or may not affect
you, but should be eliminated with a release subsequent to 4.0 (dtd 3-Nov-2005)

Finally, depending on your exact requirements, the LIKE operator in VBA may be
useful.


--ron

 

[vigi98]

You can use Regular Expressions if you use VBScript.RegExp, creating a
function that you can use.

Here is an example that validates email addresses.


'-----------------------------------------------------------------
Public Function ValidEmail(Adress As String) As Boolean
'-----------------------------------------------------------------
Dim oRegEx As Object
Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
' .Pattern = "^(\w+\.)*(\w+)@(\w+\.)+([a-zA-Z]{2,4})$"
ValidEmail = .Test(Adress)
End With
Set oRegEx = Nothing
End Function

Thanks a lot for that, it works perfectly. Now, another question: is it
possible to have regular expression variables like under unix (for
instance if you have a regexp like "aaa(.*)bbb(c+)ccc", can you use the
variables $1 and $2 afterwards ?