Using Excel Filter to Search for text containing a comma & displaying all records that contain one

[LeeNJ]

I have a list of Email Addresses in an Excel File along with other columns. I Filter the email column to search for mistakes, like double periods (..) which works fine in the filter. But when I search for a comma (,) it's not allowed. Sometimes people type a comma for the period (by accident) & I need to find these records via the Filter and correct the email address. I tried searching text that contains *,* and that doesn't work either. Any idea how to easily filter a column to search text containing a comma. Thanks in advance.

 

[JoeMo]

Maybe a formula solution would be easier. Copy the formula in B2 down to cover all email addresses in col A.

Excel Workbook
	A	B
1	EmailAddress	Comma or .. Errors
2	Someone@email.com
3	Someone@email.com
4	Someone@email..com	ERROR
5	Someone@email.com
6	Someone@email.com
7	Someone@email.com
8	Someone@email,com	ERROR
9	Someone@email.com
10	Someone@email.com
Sheet2

 

[Peter_SSs]

But when I search for a comma (,) it's not allowed.

What are the symptoms of "it's not allowed"?
Filtering for *,* works for me

 

[LeeNJ]

Joe, that you so much for answering my question. I still wish I can do it in the filter screen, but I'll try your formula. I tried adding more items that I search for, but the formula didn't work. Can you please edit it to look for a few more items that I look for in the filter. This way I won't even have to use the filter for anything; your formula will search for all.
1) .cm (instead of .com - common mistake. I know there is a country code of cm for Country of Cameroon, but I think chances are slim that person is from that country.
2) *@*@* (looking for two @ signs anywhere in the email address (sometimes instead of a period people use an @ sign by mistake or just uses two @ signs somewhere in the email address-not necessarily right next to each other, hence the astericks
3) Can you search for record if it DOESN'T contain an @ sign? Obvioiusly email is incorrect if it doesn't have an @ sign
4) Can you search for record if it DOESN'T contain a Period (.) because all emails should have a period as well as the @ sign.
Thanks for any help and thanks for your quick response yesterday

 

[LeeNJ]

Peter,
Oops, I just realized I get my list from Access and that's when I filter the records to search for bad emails BEFORE I export the list to Excel, so this way it's get's corrected in Access before I export it into Excel.
So I was actually filtering in Access. Whether I search for a , or *,* it says to "Enter a Valid Value".

Thanks for any help, if you know Access! If I can't figure it out in the filter, I can still use the formula that Joe gave me after I export the list, I'll just have to remember to make the corrections in Access as well. It would be nice to know how to do it either way.
Thanks again.

 

[JoeMo]

Joe, that you so much for answering my question. I still wish I can do it in the filter screen, but I'll try your formula. I tried adding more items that I search for, but the formula didn't work. Can you please edit it to look for a few more items that I look for in the filter. This way I won't even have to use the filter for anything; your formula will search for all.

You are welcome. That's a lot of terms to add. Would you be open to a VBA solution?

 

[LeeNJ]

I'm good. thanks for everyone's help.

 

[Peter_SSs]

So I was actually filtering in Access.

Thanks for any help, if you know Access!

I don't, but wouldn't you be best to ask this question in the Access Forum? (or perhaps you have?)

I tried adding more items that I search for, but the formula didn't work. Can you please edit it to look for a few more items that I look for in the filter.

1) .cm (instead of .com - common mistake. I know there is a country code of cm for Country of Cameroon, but I think chances are slim that person is from that country.
2) *@*@* (looking for two @ signs anywhere in the email address (sometimes instead of a period people use an @ sign by mistake or just uses two @ signs somewhere in the email address-not necessarily right next to each other, hence the astericks
3) Can you search for record if it DOESN'T contain an @ sign? Obvioiusly email is incorrect if it doesn't have an @ sign
4) Can you search for record if it DOESN'T contain a Period (.) because all emails should have a period as well as the @ sign.

If we are in Excel, then you could try this formula

<b>email check</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:223px;" /><col style="width:201px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">abc@def.com</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; ">abc@def..com</td><td style="font-size:10pt; ">Error</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; ">abc@def,com.uk</td><td style="font-size:10pt; ">Error</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; ">qwerty@abc.net</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; ">name@abc@com.fr</td><td style="font-size:10pt; ">Error</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; ">nameatgmail.com</td><td style="font-size:10pt; ">Error</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; ">something@somewhere.cm</td><td style="font-size:10pt; ">Error</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; ">forgot@anydots</td><td style="font-size:10pt; ">Error</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=IF(OR<span style=' color:008000; '>(AGGREGATE<span style=' color:#0000ff; '>(15,6,SEARCH<span style=' color:#ff0000; '>({",","..",".cm","@*@"},A2&",")</span>,1)</span><=LEN<span style=' color:#0000ff; '>(A2)</span>,COUNT<span style=' color:#0000ff; '>(FIND<span style=' color:#ff0000; '>({"@","."},A2)</span>)</span><2)</span>,"Error","")</td></tr></table></td></tr></table>