# 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.

## 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:

`=LEN(B5)-LEN(SUBSTITUTE(B5,C5,""))` • Then, press ENTER and the cell will count the uppercase E from cell B5.

🔍 Formula Breakdown

↪️ The LEN function returns the number of characters in a text string. So, LEN(B5) returns the number of characters from cell B5 => 14

↪️ The SUBSTITUTE function replaces the existing text with new text in a text string. So, SUBSTITUTE(B5,C5,””) takes the data of cell C5 (i.e. E) and replaces it with nothing (“ ”) in cell B5 (i.e. Hello Everyone) and returns => “Hello veryone

↪️ LEN(SUBSTITUTE(B5,C5,””) = LEN(Hello veryone) returns => 13

↪️ Finally, LEN(B5)-LEN(SUBSTITUTE(B5,C5,””)) = 14 – 13 = 1

So, the final output => 1 • 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

`=LEN(B5)-LEN(SUBSTITUTE(B5,C5,""))` Thus, we have counted the number of the specific characters: (e, i, and c) in Cells B5, B6, and B7 respectively.

#### 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.

`=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.

### 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

`=SUM(LEN(C5)-LEN(SUBSTITUTE(C5,{0,1,2,3,4,5,6,7,8,9},"")))` • Now, press ENTER and drag the formula down to get the numbers counted from the cells situated down. 🔍 Formula Breakdown

↪️ The LEN function returns the number of characters in a text string. So, LEN(C5) returns the number of characters from cell C5 => 9

↪️ The SUBSTITUTE function replaces the existing text with new text in a text string. So, SUBSTITUTE(C5,{0,1,2,3,4,5,6,7,8,9},””) replaces 0,1,2,3,4,5,6,7,8,9  with nothing (“ ”) in cell C5 and returns an array => {“a3dbg56u8″,”a3dbg56u8″,”a3dbg56u8″,”adbg56u8″,”a3dbg56u8″,”a3dbg6u8″,”a3dbg5u8″,”a3dbg56u8″,”a3dbg56u”,”a3dbg56u8″}

↪️ LEN(SUBSTITUTE(C5,{0,1,2,3,4,5,6,7,8,9},””))) = LEN({“a3dbg56u8″,”a3dbg56u8″,”a3dbg56u8″,”adbg56u8″,”a3dbg56u8″,”a3dbg6u8″,”a3dbg5u8″,”a3dbg56u8″,”a3dbg56u”,”a3dbg56u8″}))) returns => {9,9,9,8,9,8,8,9,8,9}

↪️ Finally, =SUM(LEN(C5)-LEN(SUBSTITUTE(C5,{0,1,2,3,4,5,6,7,8,9},””))) = =SUM(9-{9,9,9,8,9,8,8,9,8,9}) = 4

So, the final output => 4

### 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:

`=LEN(C5)-(SUM(LEN(C5)-LEN(SUBSTITUTE(C5,{0,1,2,3,4,5,6,7,8,9},""))))` • 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:

`=CharacterNo(C5)` • Next, press ENTER and you will see the result. 🔍 Formula Breakdown

Here, N means Numeric character and L means non-numeric character.
So, 1L1N3L2N1L1N => means in cell D5, there is

>> Firstly, 1 non-numeric character

>> Then 1 numeric character

>> Next 3 non-numeric character

>> Then 2 numeric character

>> After, 1 non-numeric character

>> Finally 1 numeric character

↪️ So, total non-numeric character => 1+3+1 => 5

↪️ And total numeric character => 1+2+1 => 4

• 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 #### A.N.M. Mohaimen 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  5 Excel Tips
You Never Knew  