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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
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.
 

poolf01

Board Regular
Joined
Apr 24, 2007
Messages
212
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:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,159
Messages
5,768,523
Members
425,479
Latest member
Neerajcool

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
Top