How to Count Number of Specific Characters in a Cell in Excel

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 how to count specific characters in a cell in Excel using formulas.

Download Practice Workbook

4 Effective Methods to Count Number of Specific Characters in a Cell in Excel

This section will cover 4 different and unique approaches to count number of specific characters in a cell in Excel. Let’s have a look over them.

1. Count Number of Specific Characters

The cells you are considering may contain uppercase and lowercase characters. You can easily count a special character within a cell for: uppercase, lowercase, and both for upper and lowercase criteria. We are going to use nested formulas using LEN , SUBSTITUTE , UPPER , and LOWER functions.

1.1. Count Uppercase Characters

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

We are going to count the number of the characters E, I, and C in the texts B5, B6, and B7 respectively. Here, all the lookup characters are in uppercase form. Let’s check the process of how you can count these special characters from the lookup cells.

Steps:

• First of all, type the following formula in Cell D5:

• Then, press ENTER and the cell will count the uppercase E from cell B5.

• Now, drag the Fill Handle tool to Autofill the formula downward.

• Hence, you will be able to successfully count the number of the specific characters E, I, and C in Cells B5, B6, and B7 respectively.

1.2. Count Lowercase Characters

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

For counting special characters in lowercase form, the formula will be

Thus, 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)

1.3. Count Both Upper and Lowercase Characters

There may be cases in 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.

Or,

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

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

2. Count Numbers in a Cell

Let’s consider a new dataset. Here, email ids of some users and their corresponding passwords are stated.

The passwords are generated from alphanumeric characters. We want to count only the numbers from these passwords. Here, we will use SUM, SUBSTITUTE, and LEN functions.

Steps:

• First, type the following formula in cell D5

• Now, press ENTER and drag the formula down to get the numbers counted from the cells situated down.

3. Count Characters Except for Numbers

For the same dataset, you may want to count the characters except for numbers. For this:

• Use the following formula in cell D5:

• Next, drag the formula below to copy and get the same output from the other cells.

4. Use VBA Code to Count Characters

We will now create a custom-made function for counting characters. And for this purpose, we will use a VBA code.

Steps:

• First of all, press ALT+F11 to open the Visual Basic Editor window.
• Now, select Insert and click Module.

• Then, the Module window will come up. In that window, assign the code below.

Code:

Function CharacterNo(pInput As String) As String

Dim zRegex As Object
Dim zMc As Object
Dim zM As Object
Dim zOut As String

Set zRegex = CreateObject("vbscript.regexp")

zRegex.Global = True
zRegex.ignorecase = True
zRegex.Pattern = "[^\w]"

CharacterNo = ""

If Not zRegex.test(pInput) Then

    zRegex.Pattern = "(\d+|[a-z]+)"
    Set zMc = zRegex.Execute(pInput)
    For Each zM In zMc
        zOut = zOut & (zM.Length & IIf(IsNumeric(zM), "N", "L"))
    Next
    CharacterNo = zOut

End If

End Function

This code will generate a custom-made function: “CharacterNo”.

• If you start to type the name of the function, you will see it as a suggestion.

• So, type the following formula in cell D5:

• Next, press ENTER and you will see the result.

• Dragging this formula below will count the alphanumeric characters from each cell.

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

• How to Count Characters in Cell without Spaces in Excel (4 Methods)
• How to Count Characters in Cell up to Space in Excel
• How to Count Characters in Cell Including Spaces in Excel (5 Methods)