ExcelInsider Team
Sometimes, you might need to count how many times specific characters appears in a cell in Excel. For example, you may want to check how often a certain digit appears in a product code, or find out how many times a specific letter or symbol comes in a long text. Although Excel doesn’t have a built-in function to do this directly, there are some simple methods to do it easily.
To count specific characters in an Excel cell, follow the steps below.
➤ Enter the formula: =LEN(cell_reference)-LEN(SUBSTITUTE(cell_reference,”a”,””))
➤ Replace the “cell_reference” with the cell containing the text from where you want to count specific characters.
➤ Replace “a” by the character that you want to count.
➤ Press the Enter button to get the expected count.
In this article, we’ll guide you through various methods to count specific characters in a cell step by step. The most common solution is to use the combination of LEN and SUBSTITUTE functions which is case-sensitive. Adding the UPPER function with the formula, we can make it case-insensitive. Besides, we can count the substring from the given text. Lastly, we will learn how to count the special characters in an Excel cell.
Combining LEN & SUBSTITUTE Functions (Case-Sensitive)
In the following dataset, we have some texts in Column A. Also, we put some specific characters in Column B to count its occurrences from the given text.
Let’s use the combination of LEN and SUBSTITUTE functions to count the specific characters in Excel.
Steps:
➤ Select the output cell (C2) and insert the following formula.
=LEN(A2)-LEN(SUBSTITUTE(A2,B2,""))
➤ After pressing the Enter button, use the Fill Handle tool to copy the formula till C13 cells.
Using LEN, SUBSTITUTE & UPPER Functions (Case-Insensitive)
If you want to get a case-insensitive output, you can use the following formula. Here, we have to use the UPPER function as the SUBSTITUTE is a case-sensitive function.
=LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),UPPER(B2),""))
In this formula, the UPPER function converts the entire text in A2 cell to uppercase. In that way, this formula shows the case-insensitive output as it treats both small and capital letters equally.
Alternatively, you can use the LOWER function and you’ll get the same output. In that case the formula would be:
=LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER(B2),""))
Counting Specific Text/Substring in Cell
In some cases, you might want to count a certain text or substring rather than a single character. In that situation, you have to divide the counted characters by the length of the certain text. The formula would be:
=(LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),UPPER(B2),"")))/LEN(B2)
Count the Multiple Special Characters in an Excel Cell
When you have special characters (e.g. !, #, %, & etc.) in your dataset, and you want to count these characters using a single formula. This method will be helpful for you.
Steps:
➤ First, make a list of special characters available in your dataset.
➤ Then, select the cells (e.g. D2:D11 cells).
➤ Go to the Name Box (left side of the Formula Bar), type “List_Special_Chars” and press Enter button.
➤ Now, type the following formula in the output cells (B2).
=SUM(LEN(A2)-LEN(SUBSTITUTE(A2,List_Special_Chars,"")))
FAQ
How to Count Specific Characters Across Multiple Cells?
If you want to count the occurrences of a certain character in a range of cells and then total count, you can use the following formula. Here the SUM function aggregates the count across the cell range.
=SUM(LEN(A2:A13) – LEN(SUBSTITUTE(A2:A13, “a”, “”)))
How Do You Count If A Cell Contains Part Of Text?
This article focuses the count of a certain characters in a cell. But sometimes you may want to the count the number of cell containing specific text. In that case, you can use the following formula.
=COUNTIF(cell_reference,”*a*”)
Is There Any Way to Count Specific Characters Without Using Formula?
Yes. One thing is that you can use the Find & Replace feature (shortcut: Ctrl + F ) to count the specific characters. But it is a manual process and tiresome too. Another way is to use the VBA creating a customized function.
Wrapping Up
In this article, we explored four distinct methods for counting specific characters in Excel. Additionally, we discussed scenarios where using formulas is most appropriate. Feel free to download the practice file and share your thoughts and suggestions.
