In this blog post we will uncover an Excel formula to count how many times a specific character appears in a cell.
The formula used in this tutorial will work for any text character, and can also be used to count the occurrences of specific words in a cell.
In this example, we used the formula to count the occurrences of the asterisk in the cells of column A.
The Excel Formula
The formula below was used to return the number of times the asterisk appears. It uses the LEN and SUBSTITUTE functions to accomplish its mission.
=LEN(A2)-LEN(SUBSTITUTE(A2,"*",""))
How Does it Work?
The LEN function is used to count the total number of characters in a cell.
The second half of the formula uses the LEN function again but with the SUBSTITUTE function. SUBSTITUTE replaces all occurrences of the asterisk with nothing, essentially removing them.
So what we are left with is a formula that subtracts the number of characters in the cell excluding the asterisks from the total number of characters including asterisks.
The result is how many asterisks there are in the cell.
It is a clever approach that can be applied for any word, phrase or text character.
Leave a Reply