When dealing with data that is a combination of text, special characters, and numbers, you might run across a situation where you need to count the characters in a cell. For example, you might need to count up the number of links in a block of text, check for extra spaces that could have snuck in, or count any number of specific characters that show up in a cell.

Example: Make sure every phone number in a list is formatted correctly.

In the example below, we have a list of phone numbers we want to make sure are formatted correctly (XXX-XXX-XXXX), but even at a glance we can tell there are some phone numbers that don’t have enough characters, or at the very least are missing a dash “-“. Let’s find exactly how many numbers do not have 2 dashes in them.

• Step 1: Using SEARCH and ISNUMBER to look for a dash

If we use SEARCH to look for the dash character, and then wrap that in ISNUMBER, we can see that all 5 of our phone numbers have the dash character. However, that doesn’t give us any additional information about which ones have 1 vs. 2.

• Step 2: Using SUBSTITUTE and LEN Functions together.

Instead, we’re going to get creative by using the SUBSTITUTE function along with LEN. In the picture below, the formula shown is in cell B2. The Substitute function takes in 3 arguments:

• Which cell you’re targeting (in this case A2)
• The character you want to “substitute” (the dash)
• The character you’d like to put in its place (in this case the “@” symbol).

As you can see, there no longer exists a dash character in the string at all. It has been fully replaced with “@”.

=SUBSTITUTE(A2,”-“,”@”)

This “replacement” character does not have to be the same length as the original thing you’re replacing. I could put the entire word “EXCEL” in the string if I wanted! For our purposes that doesn’t make a lot of sense, but hopefully you can better see what the function is doing now.

• Step 2a: Combining with LEN

The LEN function is one of the simplest in Excel. It simply returns the length of the cell you reference. In the picture below, we can see that the length of B2 is 11.

So what does this have to do with our problem? We’re going to replace the target character (the dash) with an empty string, measure the length, and subtract that number from our original. To recap, we have

1. The length of the original string in column B
2. The removal of the dash character in column C
   1. The formula for that is =SUBSTITUTE(A2,”-“,””). Instead of a new character as the 3rd argument, simply put double quotes.
3. The length of the shorter string in column D
4. The difference between the two in column E

Let’s combine this all into one formula

=LEN(A2)-LEN(SUBSTITUTE(A2,”-“,””))

We can clearly see now that rows 2, 4, and 6 only have one dash. To fix this particular data set by hand would not be too difficult, but imagine if there were hundreds or thousands of rows to test…? Simply fill that formula all the way and you can quickly see the troubled cells.