Regular expressions in Excel
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. |
Regular expressions in Excel
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) "vigi98" wrote in message ... 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. |
Regular expressions in Excel
On Thu, 10 Nov 2005 09:49:04 +0100, vigi98 wrote:
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 |
Regular expressions in Excel
Bob Phillips wrote:
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 ? |
All times are GMT +1. The time now is 10:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com