Excel to check for regular expression?

Hi,

How do you use Regular expression in excel? For eg: Check a column of
data to see whether are all of them valid email addresses?

Please advise

THanks

0
7/2/2007 3:21:16 PM
excel 39879 articles. 2 followers. Follow

5 Replies
439 Views

Similar Articles

[PageSpeed] 24

By valid, do you mean that the email is in proper syntax or that it is an 
actual email address.   

Text formulas can be used for proper syntax.  Perhaps a Visual Basic 
proceedure.   

I doubt you can check that the email address actually exists, as many 
companies and ISPs will simply disregard and drop mail to invalid email 
addresses to eliminate spam



"user" wrote:

> Hi,
> 
> How do you use Regular expression in excel? For eg: Check a column of
> data to see whether are all of them valid email addresses?
> 
> Please advise
> 
> THanks
> 
> 
0
7/2/2007 3:56:01 PM
This function will test that an passed emaiul address is properly 
constructed


'-----------------------------------------------------------------
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}$"
        ValidEmail = .Test(Adress)
    End With
    Set oRegEx = Nothing
End Function


-- 
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"user" <mylinuxjourney@gmail.com> wrote in message 
news:1183389676.478316.284990@d30g2000prg.googlegroups.com...
> Hi,
>
> How do you use Regular expression in excel? For eg: Check a column of
> data to see whether are all of them valid email addresses?
>
> Please advise
>
> THanks
> 


0
bob.NGs1 (1661)
7/2/2007 4:28:10 PM
"Bob Phillips" <bob....@somewhere.com> wrote...
>This function will test that an passed emaiul address is properly
>constructed

It doesn't handle every valid e-mail address. A lot more characters
than Latin letters, decimal numerals and underscores are allowed, at
least when the mailbox is inside double quotes.

>Public Function ValidEmail(Adress As String) As Boolean
....
>  .Pattern = _
>"^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
....

FWIW, this would happily match

..@-.--    and    -@-.--

which aren't valid e-mail addresses. Also, {1,} isn't as efficient
either in terms of typing or processing as +.

The very end of the e-mail address is the top-level domain, which
should only contain Latin letters but *could* span up to 6 letters
(currently as of posting date, e.g., .museum and .travel), so you want
to end the regexp with [A-Za-z]{2,6}$. However, literal IP addresses
are also supported, so the entire part to the right of the @ could be

((2([0-4]\d|5[0-5])|1\d{2}|[1-9]\d|[1-9])\.){3}(2([0-4]\d|5[0-5])|
1\d{2}|[1-9]\d|[1-9])

0
hrlngrv (1990)
7/2/2007 6:13:37 PM
On Jul 3, 2:13 am, Harlan Grove <hrln...@aol.com> wrote:
> "Bob Phillips" <bob....@somewhere.com> wrote...
> >This function will test that an passed emaiul address is properly
> >constructed
>
> It doesn't handle every valid e-mail address. A lot more characters
> than Latin letters, decimal numerals and underscores are allowed, at
> least when the mailbox is inside double quotes.
>
> >Public Function ValidEmail(Adress As String) As Boolean
> ...
> >  .Pattern = _
> >"^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
>
> ...
>
> FWIW, this would happily match
>
> ....@-.--    and    -...@-.--
>
> which aren't valid e-mail addresses. Also, {1,} isn't as efficient
> either in terms of typing or processing as +.
>
> The very end of the e-mail address is the top-level domain, which
> should only contain Latin letters but *could* span up to 6 letters
> (currently as of posting date, e.g., .museum and .travel), so you want
> to end the regexp with [A-Za-z]{2,6}$. However, literal IP addresses
> are also supported, so the entire part to the right of the @ could be
>
> ((2([0-4]\d|5[0-5])|1\d{2}|[1-9]\d|[1-9])\.){3}(2([0-4]\d|5[0-5])|
> 1\d{2}|[1-9]\d|[1-9])

 So How do we start using it in Excel? Thanks

0
7/2/2007 10:11:26 PM
as I showed.

-- 
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"user" <mylinuxjourney@gmail.com> wrote in message 
news:1183414286.769076.269750@m37g2000prh.googlegroups.com...
> On Jul 3, 2:13 am, Harlan Grove <hrln...@aol.com> wrote:
>> "Bob Phillips" <bob....@somewhere.com> wrote...
>> >This function will test that an passed emaiul address is properly
>> >constructed
>>
>> It doesn't handle every valid e-mail address. A lot more characters
>> than Latin letters, decimal numerals and underscores are allowed, at
>> least when the mailbox is inside double quotes.
>>
>> >Public Function ValidEmail(Adress As String) As Boolean
>> ...
>> >  .Pattern = _
>> >"^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
>>
>> ...
>>
>> FWIW, this would happily match
>>
>> ....@-.--    and    -...@-.--
>>
>> which aren't valid e-mail addresses. Also, {1,} isn't as efficient
>> either in terms of typing or processing as +.
>>
>> The very end of the e-mail address is the top-level domain, which
>> should only contain Latin letters but *could* span up to 6 letters
>> (currently as of posting date, e.g., .museum and .travel), so you want
>> to end the regexp with [A-Za-z]{2,6}$. However, literal IP addresses
>> are also supported, so the entire part to the right of the @ could be
>>
>> ((2([0-4]\d|5[0-5])|1\d{2}|[1-9]\d|[1-9])\.){3}(2([0-4]\d|5[0-5])|
>> 1\d{2}|[1-9]\d|[1-9])
>
> So How do we start using it in Excel? Thanks
> 


0
bob.NGs1 (1661)
7/3/2007 7:39:21 AM
Reply:

Similar Artilces:

How do I get Excel to open a previously saved file?
With Excel not opened, all of sudden when I try to open an existing Excel file, it only opens a blank workbook. I then have to repeat the action of opening the file from the blank workbook. I am not sure what has happened but I have always just been able to just click on the Excel file I want to open and it would automatically open Excel and the intended file. Please advise hi, check this....... Tools>options>general Tab... Make sure that Ignore other applications is unchecked. that's all i can think of Regards FSt1 "Jeff S" wrote: > With Excel not opened, all of ...

Excel 2007 3 Traiffic Light Conditional Formatting
Does anyone know how to get this to work using the 3-light conditional formatting in Excel 2007 using the reference to another cell? I need it pretty much the same way as listed above, but it is not working for me. Here is the set up I currently have. ROW 1 MTD ACTUAL BUDGET DIFFERENCE ROW 2 120 120 130 -10 I want the 3-Light Conditional Formatting to be used on the MTD column with this formula set up below. GREEN LIGHT >= =D2=0 YELLOW LIGHT >= =D2=(-3) RED LIGHT Why is this not working? Any and every bit of help would be most appreciated! Thanks, nathan Nathan, AFAIK, you ca...

filtering check boxes
I know how to filter a field that is not a check box, I use the simple code below Private Sub Label41_Click() Me.Filter = "[Month] Like 'Jun'" Me.FilterOn = True End Sub However how can I use this code to filter a check box. Check box is either "Yes" or "No" I tried using Yes, and True but that does not filter. Any succestion? Sandrao Try using -1 instead of True -- Dave Hargis, Microsoft Access MVP "sandrao" wrote: > I know how to filter a field that is not a check box, I use the simple code > below > Private Sub Label41_Clic...

Excel PivotCHarts
How do I convert a pivot table to a pivot chart My colleague Debra Dalgleish has posted this tutorial on my web site: http://www.geocities.com/jonpeltier/Excel/Pivots/pivotcharts.htm IMHO, pivot charts are a great idea whose time hasn't yet come. There is a limited choice of chart types, the chart can be formatted only in limited ways (you can't change the PLOT AREA!), the whole pivot table must be included, and whatever formatting you may have applied is obliterated when the pivot table is updated. I generally make regular charts from pivot table data. In Excel 97, whic...

Encrypt Text in Excel
I want to encrypt text in Excel. I want the character spacing to be accurate, I just want the actual text to be encrypted to a user. Anyone know any tricks, shortcuts, add-ins to do this? Thank you! Marc One more note. It is the text in a given range of cells that I want to encrypt. I've tried looking at different fonts and then locking the cells (protecting worksheet) but none of the built-in Wingding fonts keep the character spacing intact. I thought that maybe there was a way to do a custom number or text format but wasn't able to get that to work. "Marc" wrote: ...

Why does spell check change to English(US) on replies/forwards?
My default language is set to English(UK) spell checker reverts to English (US) when replying or forwarding messages. How can I change this so it is always English(UK)?Cheers,Tom ...

link Access workbook to Excel workbook
Can I link information from an Excel worksheet to an Access worksheet? Hello, In Access, clic on File menu, External data, link tables and follow each step. You'll have to define first row as heading label and that's it. regards "Toinett" wrote: > Can I link information from an Excel worksheet to an Access worksheet? ...

run an excel file remotely
Hi all i got an excel application that generates reports from underlying pivot tables. the reports are generated using excel 2003. but most of the clients donot have licenses for 2003 and some of the features of 2003 cannot be used when the file is opened from the client system. for example GETPIVOTDATA is not very flexible as it was in 2003 version. So is it possible to run the excel application from the server without being copied to the client system. This also helps in maintaing single version of the excel file rather than multiple versions on each client system. Thanks in adva...

Excel 2000 #15
We have a situation where all of the text keyed into a cell does not appear when viewing or printing the spreadsheet. If the user merged a row into columns from J through W, it all appeared. If we inserted hard returns into the entry into the cell, it also displayed. Has anyone had similar a experience? If so how did you resolve it? Thank you. Raymond Specs for Excel states that 32,767 characters can be placed in a cell. 1024 will be visible or can be printed. Using ALT + ENTER to get hard returns will increase this limit. Gord Dibben Excel MVP On Wed, 14 Apr 2004 07:20:58 -07...

Budget Wizard for Excel
We're running Dynamics GP 10.0 Advanced Management Edition. The Budget Wizard for Excel is not checked as a registered module. Is this separately licensable? I thought the budget/excel import export utility was a built-in feature. The export functionality works but we're unable to import an excel budget. Can someone shed some light on this issue? Thanks. Dear Aman, Go to Microsoft Dynamics GP menu >> Tools >> Setup >> System >> Registration, and make sure you didn't uncheck it from the selected modules. Regards, -- Mohammad R. Daoud MCP, MCBMSP, MCTS...

Pictures in Excel cells
Dear friends I want to create a book with personal descriptions, including pictures. But how can I get a picture in an Excel cell? I thought that =HYPERLINK("file.jpg") would be the thing, but the pictures are not retrieved when I combine the Excel sheet to the final Word file. Please help. -- Jos´┐Ż Do you object if ... Alt > i > p > f and select the picture what you want to import. HTH -- MRT "Jose" <jose@127.0.0.1> wrote in message news:e4nsg59mm09cuqgr2etqtm06jm87idk343@4ax.com... > Dear friends > > I want to create...

Smart List export to Excel #2
Hi, We are using Dynamics 9.0 and Office 2003. When we try to do a Smart List export to Excel, we keep getting an "Exception_Class_Object_Exception" that references varying object errors, such as 'Cells' or 'Value'; the object errors will change each time we try an export. As a workaround, I've found that closing Excel before doing the export allows the export to complete successfully. However, that solution isn't acceptable, as our Dynamics users would like to be able to work in Excel, doing other tasks, while the export is processing. Does anyone...

Excel 2002 configuration
This is a multi-part message in MIME format. ------=_NextPart_000_00E6_01C3ECD1.C34FE920 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is there any way to disable the little green corner marker in the top = left of cell for all cases where the formula references empty cells?=20 Thanks, Alan ------=_NextPart_000_00E6_01C3ECD1.C34FE920 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD>...

excel html user area import
Hello, Im using excel 2007 and having problems with importing html. Problem is that I need to import data from html page which is in user area( username and password protected). So data imports ok, but when I exit saved excel file and then open it again , it can't refresh data because i'm not logged in. How can i solve this problem? Can I add some html form to excel so I can logon with that form, so I can refresh data? Thank you! ...

Excel 2007 slow saving with defined names
Hello, I am using a worksheet with a lot of defined names. Saving this sheet in Excel 2007 takes about 5 minutes! In previous Excel-versions the same worksheet saves within 10 seconds. Does anyone have an idea to improve this performance? Probably Windows Vista has something to do with this problem, because: - Windows XP and Excel 2007: fast saving - Windows Vista and Excel 2003: fast saving - Windows Vista and Excel 2007: slooooow saving Any ideas? Greeting RI ...

Excel charts in print preview distorted.
I'm assisting a customer with an excel presentation she created on another computer. She is curently using Excel XP and she changed some of the data the charts are pulling from and now the charts are all distorted in print preview. The charts are all scrunched up in the upper portion of the page. I view this presentation on my computer and all looks fine in page layout and print preview. I tried setting her default printer to mine, no help. I tried adjusting her video resolutions (they were originally the same as mine) to higher and lower resolutions and get the same results. Any...

Excel Automation Resizing.
Hello, I am wondering if anyone knows how to have an excel sheet fit entirely in a window's view for an MDI application, and resize itself when the view is resize. I would appreciate any help you can give. Also, is there a website that has the Excel object model interfaces for MFC? Thanks, Markus. ...

How do I get email in Outlook Express on an XP machine into Outlook 2007 on Windows 7 Machine?
The instructions for importing Outlook Express files from another machine assume that Outlook Express can be run on the target machine. I don't believe there is a way to do that on a Windows 2007 machine. So How do I accomplish this? On Fri, 20 Nov 2009 18:03:05 -0800, MikeH <mikeh@somewhere.net> wrote: > The instructions for importing Outlook Express files from another > machine assume that Outlook Express can be run on the target machine. I > don't believe there is a way to do that on a Windows 2007 machine. So > How do I accomplish this? F...

set excel always on top
Can I set excel to always be on top? I am using excel with a half screen and other programs and want excel to be on top as I switch between the other programs. TIA Todd L. that is a windows question. this is excel workgroup. >-----Original Message----- >Can I set excel to always be on top? I am using excel with a half screen and >other programs and want excel to be on top as I switch between the other >programs. > >TIA > >Todd L. >. > Take a look at this post: http://groups.google.com/groups?threadm=3D3DD50A.CF0CACC5%40msn.com Todd wrote: > &g...

Excel password #2
Hi all, I've an old worksheet in Excel 2k, and I forgot the password I saved with it. Is there a way to retrieve it? thanks N! Xau N! Xau, Workbook password to open???? If so, here's a post by Norman Harker that should give you a start. John <snip> Hi! If it is a workbook password then here is the standard post on that one: The following sites advertise ability to recover, amongst other things, Excel file passwords. These passwords are imposed when saving Excel files and are not to be confused with *internal* passwords on sheets and workbook structure. http://www.i...

zones in excel charts
I'd like to be able to set up zones in an Excel chart. Imagine a bell curve. Areas under the curve within one standard deviation could be green, those between one and two standard deviations would be yellow, and those more than two would be red. Or I can have an X - Y scatter chart. If the X-axis runs from 0 - 10, I might want 0 - 3 as red, 3 - 5 as green, and 5 - 10 blue. Just as an example. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button...

Word Wrapping in Excel
I am having trouble with Word Wrapping in Excel. It does not adjust th row height properly. I have tried Format, Rows, AutoFit and it stil doesn't work. Help, pleas -- Mandy1 ----------------------------------------------------------------------- Mandy11's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1300 View this thread: http://www.excelforum.com/showthread.php?threadid=27632 Hi do you experience this with merged cells. If yes->nothing you can really do about this without using vBA -- Regards Frank Kabel Frankfurt, Germany Mandy11 wrote: > I...

Excellent Resource
New material has been added providing knowledge of Test Driven Development in Microsoft Dot Net with information about creating web applications with ADO.Net and Asp.Net..... http://www.dev-sphere.com/microsoft/mcsd/mcsd.htm "methew.brain@gmail.com" <methew.brain@gmail.com> wrote in news:e6090bac- 846a-490c-8307-8596a7b10567@e6g2000prf.googlegroups.com: > New material has been added providing knowledge of Test Driven > Development in Microsoft Dot Net with information about creating web > applications with ADO.Net and Asp.Net..... > > http://www.dev-sphere.co...

How do i print an excel file in the form of a booklet?
I have a list of phone numbers and addresse that I need to print in the form of a small booklet that I can carry in my purse. How do I print it? What program and how to set it up? Also I want ohotos on the left and data on the right side pages. I'm not sure excel is the best application for this kind of thing. I'm sure you could do it (after a few hours/days/months of frustration!), but maybe you could find a better application by searching google. Or even put the data into MSWord and use that for all the nice formatting. shireen wrote: > > I have a list of p...

Dividing a cell in Excel 2000
I am embarrassed to ask, but here goes. About a year ago some of you on this forum explained how I can divide a cell without creating a whole new row. I can't remember how I did it. With a diagonal line? A horizontal line? A vertical line? Doesn't really matter; I just want to enter a number on each side of the divide. Much appreciation, Lois You can always find previous posts in the archives. Just enter your name or email address in the author field at http://groups.google.com/advanced_group_search?q=group:*excel* to see your posts and any replies. You might take ...