Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

3 Answers 3

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
add comment

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.