The “Find Characters” menu helps you to answer in a couple of clicks whether a cell contains certain characters or not.
It seems like a small task to find a specific symbol or sequence in an Excel cell. Let’s list the most popular options available in Excel:
- The Find and Replace procedure will find characters in a book, worksheet, or range
- The SEARCH and FIND functions indicate the position of the searched symbols within a cell
- The SUBSTITUTE function can replace characters with a blank. If it succeeds, the string will become shorter, and this can be easily detected with LEN function
- Filtering will decrease visible rows to only those that contain your search pattern, which can be a single character or a multiple character string
- MATCH function (with wildcards) will return the address of a cell containing the sought symbol
- The all-powerful VLOOKUP function (when used with wildcards) will return the value of the first cell containing the symbol(s) or the one to the right
Sometimes there are certain difficulties when searching for “?” and “*” characters, see the related article: How to find asterisks in Excel.
However, the listed options are not suitable and convenient for all types of characters. When you need to search for sets of characters, rather than just 1 or 2, the use of standard procedures and functions can be time-consuming and complicated.
It is for such cases that I have developed intuitive procedures in my add-in.
Finding Characters in Excel with !SEMTools
I’ve separated out of all the popular search procedures the search for symbols by type and by lettering and made separate menus for them:
You can read more about the procedures in the related articles below.
Find symbols by type
How do I find text characters in a cell?
How to find numbers in a cell;
How to find out if a cell contains Latin characters;
How to find capital letters in a cell.
Find characters by font style
Sometimes users need to identify whether cells contain characters in a particular font style — bold, italic, or underlined. In !SEMTools there’s a procedure for every case.
Searching for characters using regular expressions
Regular Expressions are not available in Excel as a standard feature, but a bit of coding allows you to enable their support. Which is what was done for add-in users. Knowing them, you can find almost any characters in cell text.
In addition to their presence itself, a nice bonus is that the REGEXREPLACE, REGEXTRACT, and REGEXMATCH functions are available for free in both the full and basic versions.
Usually, once you have managed to find certain characters or combinations of characters in cells, other operations on them follow. For example, you may extract them, delete them or replace them with some other characters (e.g. replace diacritic characters with latin). The corresponding sections of the site will help in solving such problems:
- Remove unnecessary characters in Excel;
- Extract certain characters in Excel;
- Change symbols in Excel