Count How Many Times a Specific Character Appears – Excel Formula

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.

Watch the Video

More Excel Formula Tutorials

• Excel formula to extract a postcode from a UK address
• How to use the TEXT function in Excel
• Formula to match records on different worksheets
• Excel timesheet with different rates for shift work