How can I find number of characters within a cell in excel?

excel

#1

Hi,

I want to find the number of given character(s) in a excel cell. For Example: Let’s say cell A2 has string “I am practicing advance excel functions”, Here I want to find number “e” in cell A2. Is there any specific function to perform this?

Regards,
Ravi


#2

@Ravi,

I don’t think there is any direct function to solve this problem in excel. It can be achieved in two ways:

  • Write a VBA program/ user defined function
  • Use existing functions (Substitute and Len)

I always prefer the second approach as I always suggest do not write VBA program unless it is compulsory. In this case, it is not.

As you mentioned cell A2 has string “I am practicing advance excel functions”. Follow below steps:

  • Find the length of cell A2, which is 39
  • Substitute “e” with blank using substitute function in cell A3. Write formula as:
    =SUBSTITUTE(A2,“e”,"")
  • Calculate the length of cell A3, which is 36
  • Now, 39-36=3, total number of “e” is 3.

Hope this helps!

Regards,
Sunil