Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening two separate instances of Excel | Excel Discussion (Misc queries) | |||
Excel startup switches | Excel Discussion (Misc queries) | |||
Stop Excel Rounding Dates | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel |