find cells that don't contain certain values

poolf01

Board Regular
Joined
Apr 24, 2007
Messages
212
I have a list of email addresses. However, some of the addresses only contain a name and are missing the @ symbol. Is there a formula I can write to find all cells that do not contain @?

Thanks
:wink:
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Try:
=FIND("@",A1)
For example:
Excel Workbook
AB
1myname@mycompany.com7
2myname@mycompany.com7
3myname@mycompany.com7
4myname@mycompany.com7
5myname mycompany#VALUE!
6myname@mycompany.com7
7myname mycompany#VALUE!
8myname@mycompany.com7
9myname@mycompany.com7
10myname@mycompany.com7
11myname@mycompany.com7
12myname mycompany#VALUE!
13myname@mycompany.com7
14myname@mycompany.com7
15myname mycompany#VALUE!
16myname@mycompany.com7
Sheet1
 
Upvote 0
How about:
Code:
Sub find()
For Each c In Worksheets(1).Range("A:A")
  If Not UCase(c.Value) Like "*@*" Then
    c.Offset(0, 1).Value = "X"
  End If
Next
End Sub

Result:
Will place an X where @ ISNT present in adjacent column B.

Chnage range accordingly.
 
Upvote 0
Thanks guys - actually went with the formula in the end, but will bear that code in mind for future reference - its a good one to know! :biggrin:
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top