Count the Number of Specific Characters in a Cell in Excel (2 Approaches)

While dealing with MS Excel you might need to count the number of specific characters in a cell in Excel. There are several techniques to perform this task. This tutorial will demonstrate 2 methods to count specific characters in a cell in Excel using formulas.

Download Practice Workbook

2 Methods to Count the Number of Specific Characters in a Cell 

We are going to use nested formulas using LEN and SUBSTITUTE functions.

The basic formula we are going to use is:

=LEN(text)-LEN(SUBSTITUTE (text, new_text, replace_with))

Before we start, we are giving you a summary of these functions:

LEN

This function gives you the length of a text.

SUBSTITUTE

You can replace any number of characters of a string with new characters.

Now, we are going to show you both case-sensitive and case-insensitive approaches.

1. Count Specific Characters’ Number in a Cell in Excel: Case-Sensitive Approach

The formula we are using is:

=LEN (text) - LEN(SUBSTITUTE (text, new_text, replace_with))

The formula above will give you the exact count of characters that you want. It will only count case-sensitive values.

Let’s assume that we have the following data set.

Count Specific Characters' Number in a Cell in Excel: Case-Sensitive Approach

We are going to count the number of the characters E, I, and C in the texts in B5, B6, and B7 respectively.

Count Uppercase:

Step 1: 

Type the following formula in Cell D5:

 

Step 2: 

Press Enter. 

Step 3: 

Drag the Fill Handle (little box-like shape) over the cells D6 and D7.

Count Specific Characters' Number in a Cell in Excel: Case-Sensitive Approach

Thus we have successfully counted the number of the specific characters E, I, and C in Cells B5, B6, and B7 respectively.

Count Lowercase:

Again, we are going to use the same data set. In this case, we are going to count the lower case characters.

Count Specific Characters' Number in a Cell in Excel: Case-Sensitive Approach

Step 1: 

Type the same formula, we used earlier, in Cell D5:

=LEN(B5)-LEN(SUBSTITUTE(B5,C5,""))

Step 2: 

Press Enter. 

Step 3: 

After that, drag the Fill Handle over cells D6 and D7.

Count Specific Characters' Number in a Cell in Excel: Case-Sensitive Approach

Finally, we have counted the number of the specific characters e, i, and c in Cells B5, B6, and B7 respectively.

Read more: Excel VBA: Count Characters in Cell (5 Methods)

2. Count Specific Characters’ Number in a Cell in Excel: Case-Insensitive Approach

There may be cases that you have to count a character in a cell. But you don’t need the exact match. You just want to know whether the character is present in the cell or not.

In such a case, we are gonna use a formula similar to the formula we used earlier. We are gonna combine the functions LOWER OR UPPER with that formula.

Formula:

=LEN(B5)-LEN(SUBSTITUTE(LOWER(B5),LOWER(C5),""))

Or,

=LEN(B5)-LEN(SUBSTITUTE(UPPER(B5),UPPER(C5),""))

The UPPER or LOWER function will change the whole text and character into upper or lower case respectively.

Step 1: 

First of all, write the formula in cell Count.

Count Specific Characters' Number in a Cell in Excel: Case-Insensitive Approach

Step 2: 

Then, press Enter. 

You can see the result in the following screenshot.

Step 3: 

Drag the Fill Handle over Cell D5 and D6.

Count Specific Characters' Number in a Cell in Excel: Case-Insensitive Approach

Read More: Count Specific Characters in a Column in Excel: 4 Methods

Conclusion

Hope you will find all these methods instrumental. The workbook is there for you to download and practice yourself.  I definitely hope that it will be helpful for you. Don’t forget to check our website Exceldemy.com for various Excel-related problems and their solutions. If you have any questions, comments, or any kind of feedback, please let me know in the comment box.


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo