Microsoft Excel - validate email id when i enter in excel cell without using macro

Asked By kiruba .e on 22-Feb-12 08:10 AM
Hi,

i want to validate email id in excel cell without using macro.  how to do this

thanks
kiruba.e
kalpana aparnathi replied to kiruba .e on 22-Feb-12 08:20 AM
hi,

use Below code:

Public Function ValidEmail(pAddress 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(pAddress)
  End With
  Set oRegEx = Nothing
End Function


Regards,
Venkat K replied to kiruba .e on 22-Feb-12 10:57 AM
You can use Excel filters to filter the invalid emails, follow the below steps to filter the invalid emails:

To filter for invalid email addresses in Excel,
 
1. From the Data menu, select Filter and then AutoFilter. This will add a drop-down list box to each of your columns.
 
2. Click the down arrow control in the top right of the column containing your email addresses.
 
3. From the drop-down menu, select (Custom…).
 

4. In the Custom AutoFilter dialog, enter in two filter criteria using the “does not contain” selection. Make certain to use the Or radio button option.
 

5. Click OK.
 


Thanks
Pat Hartman replied to kiruba .e on 22-Feb-12 04:35 PM
Excel considers all code to be a macro.  What prompted this question?  Are you trying to get around a trust issue?