I'm trying to filter a column to show cells that contain @ (as in an email address) but when I put @ in the filter box it comes up with a blank.
Any ideas?
An example spreadsheet here. Here is also a screenshot:
Google Sheets – Filter Column for Cells Containing ‘@’
google sheets
Related Solutions
Use the following formula to accomplish that.
Formula
=QUERY(
'GR Live Campaign Tracker'!J:L, // data range
"SELECT J, COUNT(L) WHERE L IS NOT NULL GROUP BY J", // select
0 // headers
)
Screenshot
Example
Added the formula into your example file.
You can filted by regexmatch instead of equality.
=countunique(filter(Sample!C2:C, regexmatch(Sample!A2:A, "Nov")))
Matching the regular expression "Nov" simply means containing "Nov" somewhere in the string.
Related Topic
- Google Sheets – Filter Multiple Values Using Condition Tool
- Google Sheets Conditional Formatting – Conditional Format for Column Headers Where All Cells Are Numbers
- Google-sheets – Compare value in all cells in a column in Google Sheets
- Google-sheets – Help needed with script that auto-updates cells in column in Google Spreadsheet showing last modify date
- Google-sheets – Getting #DIV/0 error in Google Sheets for AVERAGE, SUM, COUNT, etc on column generated by ARRAYFORMULA
- Google-sheets – How to count the amount of non-blank cells in a row, including only cells that are in a column that contains a specific value
- Google-sheets – Create serial count in column if adjacent cell is blank (Google Sheets)
Best Answer
The characters you enter in the filter box are matched to the beginning of any word in the cell content. So, @ will match "a @ b" or "a @b", but not "a@b".
To filter based on a pattern that is not a beginning of a word, you can use a workaround with
REGEXMATCH, e.g., entering in another column:and then filtering based on that column.
However, the presence of @ does not guarantee you have a valid email there. I recommend using the
ISEMAILfunction for accurate filtering: create a column withand then filter on TRUE value in that column.