Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am using Microsoft Excel 2010. How can I select all cells that aren't e-mail addresses? I want to delete them and keep only the cells that validate to e-mail addresses.

share|improve this question
    
Nice edit, @Lord T. Nadeen, your first question was improved by the community as well. Please use that as feedback. Thanks! – Arjan Sep 30 '11 at 15:58
up vote 4 down vote accepted

Validating email addresses is a black hole. And even if you were able to determine that validity of the email, you still won't know if it's a working email, or even that person's email (they may have entered someone else's address).

If you still want to do a basic test of syntax the you can go into VBA (may require enabling on the Developer tab on the ribbon) and set a reference to "Microsoft VBSript Regular Expressions 5.5", create a new module and enter this code (I am not skilled in RegEx so I got it from StackOverflow, you can get other RegExs at FightingForALostCause or a full descussion at Regular-Expressions):

Public Function CheckEmailSyntax(ByVal Email As String) As Boolean
    Dim regex As RegExp
    Set regex = New RegExp
    regex.Pattern = "^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,6})$"
    regex.IgnoreCase = True
    CheckEmailSyntax = regex.Test(Email)
End Function

Then in your project enter something like =CheckEmailSyntax(A1). It will return TRUE for the good ones and FALSE for the bad ones. Sort both columns by the T/F column and delete the FALSE ones. Hope this helps.

share|improve this answer
    
+1 Nicely done. – brettdj Sep 30 '11 at 3:17
2  
Nice. But for example, surely the plus-character is used quite a lot in email addresses nowadays. So: beware for false negatives when using such simple regex. – Arjan Sep 30 '11 at 16:07
    
@Arjan Very true. I added links to other (perhaps better?) regexs. The problem remains as I warned, you still don't know if all the 'good' emails are in use, and it they are for the person you think. – Ryan Clarke Sep 30 '11 at 16:32

This worked for me:

  1. Go to Editing on the home ribbon.
  2. Click Find and Select.
  3. Enter @ in Find what:.
  4. Click Find All.
  5. In the results, press Ctrl + A to select entire group in the results.
  6. Click Copy on the home ribbon.
  7. Paste to a new column.
share|improve this answer
    
+1 those simple tricks are stunning even after years. Ok, it has some downsides. For example what if non-mail cells have a @ char. Or you want all mail cells in a single target column. But nevertheless, this is good. – nixda Feb 11 '13 at 19:49

I would use the Find & Select/Data Validation.

  • On the Home tab, go to the Editing Group.
  • Choose "Find & Select"
  • Choose Go to Special
  • Choose Data Validation
  • Choose Same or All
  • Click OK

The cells that contain data validation will be highlighted.

share|improve this answer
    
no i dont want that , i dont want a cell that contains a data validation , i want to select all the cells that doesn't match to an email pattern – Nadeem Khedr Sep 29 '11 at 20:05
    
-1 didnt address actual question. – brettdj Sep 30 '11 at 3:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .