The Find Characters menu of the !SEMTools add-in answers one question in a click: does a cell contain certain characters or not? Digits, letters, uppercase, bold text, or any pattern you can describe with a regex.
Built-in ways to find characters in Excel
Finding a specific character or sequence in an Excel cell seems like a small task. Yet Excel offers a whole zoo of functions and tools for it. Here are the main built-in options:
- Find and Replace (Ctrl+F) finds characters in a workbook, a sheet or a selected range.
- The SEARCH and FIND functions return the position of a character inside a cell. SEARCH ignores case, FIND is case-sensitive.
- The SUBSTITUTE function (a roundabout way, but it works) replaces the characters with nothing. If the string got shorter, the character was there, and the LEN function shows it.
- Filter → Text Filters → Contains hides the rows without the character.
- MATCH and VLOOKUP with wildcards (“*@*”) return the first cell or row that contains the character.
For example, this formula returns TRUE if cell A1 contains the “@” sign:
=ISNUMBER(SEARCH("@",A1))
And this one counts how many times “@” occurs in the cell:
=LEN(A1)-LEN(SUBSTITUTE(A1,"@",""))
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Text | Has “@”? | How many “@” | Has “?” (with ~) |
| 2 | info@site.com | TRUE=ISNUMBER(SEARCH("@",A2)) | 1=LEN(A2)-LEN(SUBSTITUTE(A2,"@","")) | FALSE=ISNUMBER(SEARCH("~?",A2)) |
| 3 | a@b@c | TRUE=ISNUMBER(SEARCH("@",A3)) | 2=LEN(A3)-LEN(SUBSTITUTE(A3,"@","")) | FALSE=ISNUMBER(SEARCH("~?",A3)) |
| 4 | no at sign | FALSE=ISNUMBER(SEARCH("@",A4)) | 0=LEN(A4)-LEN(SUBSTITUTE(A4,"@","")) | FALSE=ISNUMBER(SEARCH("~?",A4)) |
| 5 | Is it 50%? | FALSE=ISNUMBER(SEARCH("@",A5)) | 0=LEN(A5)-LEN(SUBSTITUTE(A5,"@","")) | TRUE=ISNUMBER(SEARCH("~?",A5)) |
SEARCH returns a position or an error; ISNUMBER turns it into TRUE/FALSE. SUBSTITUTE removes every “@”, and the length difference is their count. Without the tilde, SEARCH("?",A2) would match any character and return TRUE for every non-empty cell.
Searching for “?” and “*” is tricky, because Excel treats them as wildcards: put a tilde before them (“~*”). The same Find and Replace article explains it in detail.
To be fair, each of these options takes effort: you have to remember the syntax, type the formula, fill it down the column and then get rid of the helper column. And when you need to search for whole sets of characters, such as any digit, any uppercase letter or any letter with an accent, the formulas quickly become long and fragile. That’s exactly why I created separate procedures for these cases in my add-in: select the column, click, done.
How to find characters with !SEMTools in 3 steps
- Copy the source column, if you want to keep the original data.
- Select the cells and open the Find characters menu on the !SEMTools tab.
- Choose what to look for: a character type, a font style or a regex pattern. Each cell gets TRUE or FALSE, right in place, and you can filter by this value.
Finding characters in Excel with !SEMTools
I split the most popular character searches into two groups and made a separate menu for each: by character type and by font style. Each procedure has its own article with formulas and examples.
1. Find characters by type
- How to find text characters in a cell: cells with real text, not just spaces or punctuation. ISTEXT can’t tell them apart.
- How to find numbers in a cell: product codes like “ABC123”, order numbers in comments, addresses with house numbers.
- How to find letters and numbers in a cell: separate meaningful values from cells with only dashes, spaces or symbols.
Once you find what you were looking for, see what you can do next with these characters at the end of this page.
There are two submenus in FIND → Characters → By type. Return TRUE (faster) gives a TRUE/FALSE check for alphanumeric characters, Latin letters or numbers. Highlight by color leaves the text as is and colors the Latin letters or the digits inside it.
2. Find characters by font style
Sometimes you need to find out whether cells contain characters in a certain font style: bold, italic or underlined. Excel formulas can’t see formatting at all, so each style has its own procedure.
The most popular one is to find bold text in Excel: headings, priority items or notes that someone highlighted inside a long list.
Path: FIND → Characters → By font style, then Bold, Italic or Underlined.
3. Find uppercase letters
Capital letters often mark names, abbreviations, headings or data entry errors, like a name typed in all caps. The procedure to find uppercase letters in cells marks every cell that contains at least one capital letter, whether the whole cell is in caps or only part of it.
Path: FIND → Characters → Uppercase.
Search with regular expressions (regex)
Before Excel 365, regular expressions were not available in Excel at all, but a bit of coding made it possible to add them. That’s what I did for the add-in users. Once you know the syntax, you can find almost any characters in cell text.
A nice bonus: the REGEXMATCH, REGEXEXTRACT and REGEXREPLACE functions are free in both the full and the free version of !SEMTools and work in Excel 2010 and later. For example, this formula checks whether a cell contains a code of 2 capital letters followed by 3 digits, like “AB123”:
=REGEXMATCH(A1,"[A-Z]{2}\d{3}")
| A | B | |
|---|---|---|
| 1 | Text | [A-Z]{2}\d{3} |
| 2 | AB123 | TRUE=REGEXMATCH(A2,"[A-Z]{2}\d{3}") |
| 3 | ab123 | FALSE=REGEXMATCH(A3,"[A-Z]{2}\d{3}") |
| 4 | XY9876 | TRUE=REGEXMATCH(A4,"[A-Z]{2}\d{3}") |
| 5 | Order AB123 | TRUE=REGEXMATCH(A5,"[A-Z]{2}\d{3}") |
Two capital letters followed by three digits, anywhere in the cell: “XY9876” matches too (XY987). Add ^ and $ to check the whole cell. In Excel 365, use REGEXTEST(A2,"[A-Z]{2}\d{3}").
In Excel 365, the built-in REGEXTEST function does the same. The syntax and dozens of ready-made patterns are in the article on regular expressions in Excel.
What’s next after finding characters?
Once you’ve found certain characters or combinations in cells, other operations usually follow: you extract them, delete them or replace them with something else. The related sections of the site will help:
- Remove characters in Excel: punctuation, digits, extra spaces, special symbols.
- Extract characters in Excel: from the beginning, the end or a given position in the text.
- Change characters in Excel: letter case, numbers saved as text and more.
If you need to look not for characters but for whole words or entire cells, see how to find words in Excel and find cells by several conditions.
See also:
Check whole columns for digits, uppercase, bold text or any regex pattern right in place, without formulas.
!SEMTools solves this and hundreds of other text tasks in a couple of clicks!
Summary: which way to find characters
I hope you found what you were looking for on this page and chose the right way to find characters in your cells. A formula with SEARCH makes sense when the check must recalculate automatically as the data changes. For everything else, the !SEMTools procedures are faster: select the range, choose what to find, and get TRUE or FALSE right in place, without remembering any syntax. For complex patterns, use regex.
All search tools of the add-in are collected in the Find section.