Select all Excel cells that do not contain e-mail addresses

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.