Custom Filter

[dineshsachidananda]

Greetings all !! good day

I need your expertise help with the attached sheet. Column A has a list of emails, and I need way to custom filter the column to cells which only contains 'special characters except '.' i.e 'gmail,.com, also, cells which contains spaces in domain i.e. 'g mail.com'

Please help

[sktneer]

Make a helper column, say col. B and then use the following Array Formula (which requires confirmation with Ctrl+Shift+Enter instead of Enter alone).

In B2

Please Login or Register  to view this content.

This will give you 0's and 1's in the helper column, not apply the filter to show only 1 in col. B.

For details refer to the attached file.


********************************
Remember that an Array Formula is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[dineshsachidananda]

Thanks a ton Sktneer !!! cheers

This works exactly the way I wanted. Also, is their a way we can extend the filter to accommodate space in any part of the email as well?

Await your expert response ! tx

[sktneer]

Ideally a domain name doesn't contain a space in it. So you may add another logical test within the OR to see if there is a space in the domain name.
Change the previous formula with the following one.......

Please Login or Register  to view this content.

If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

[sktneer]

While taking a space into consideration, the above suggested lengthy formula can be replace by a very simple formula like below (not an array formula)

Please Login or Register  to view this content.

So no need of the previous formula. You can refer the attached workbook for newly added helper column and filter the new helper column for 1.

[dineshsachidananda]

thanks once again.. that works like a charm... it made my day.....

[sktneer]

You're welcome Dinesh! Glad I could help.