Counting How Many Times Specific Character Appears in Cell in Excel 2010

In this article we will learn counting how many times specific character appears in excel 2010.

To return the count of specific character in a cell we need to use a combination ofthe “LEN”, “TRIM”, and “SUBSTITUTE” functions..

LEN:Returns the number of characters in a text string.

Syntax of “LEN” function: =LEN (text)

Example: Cell A2 contains the text “By hook or by crook”
=LEN (“By hook or by crook”) will return 19

img1

 

TRIM:Trim function is used to remove additional spaces from the text in a cell.

Syntax of “TRIM” function: =TRIM (text)

Example: Cell A2 contains the text “By hook or by crook”
=TRIM (“By hook or by crook”) will return “By hook or by crook” without any extra leading and trailing spaces.

img2

 

SUBSTITUTE:This function is used to replace the old text with new text.

Syntax of “SUBSTITUTE” function:
=SUBSTITUTE (text,old_text,new_text,[instance_num])

Example: Cell A2 contains the text “By hook by crook”
=SUBSTITUTE (A2, A2,”By hook or by crook”)
will return “By hook or by crook”.

img3

 

Let’s take an example & understand how we can count a specific text in a cell:-

We have a text in cell A2, and we want to count the specific word characters “e” from the text. Follow the below given steps:-

 

img4

 

  • Select the cell B2 and write this formula.
  • =IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,”e”,””))) and press Enter on the keyboard.
  • The function will return 5, which means the character “e” is repeated 5 times in the cell A2.

 

img5

Users are saying about us...

  1. Your tip really helped me out. I wanted to the number of items in a cell that were separated by a comma. If there was only one item, I wanted a blank. I enclosed your tip and got the answers I wanted. with the ‘comma’ in H2 I wrote:
    ‘=IF(LEN(G4)-LEN(SUBSTITUTE(G4,H$2,””))=0,””,LEN(G4)-LEN(SUBSTITUTE(G4,H$2,””))+1)

    • =(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(B1),””)))/LEN(B1)
      Where A1 is the cell to calculate
      B1 will be holding the character to be counted from A1

      substitute formula returns the the new text instead of old text.

  2. Wonderful. You could simple state that the idea is to find, how much shorter the string becomes, if you replace a specific character with “”. If searching for the count of multi-character string within text, then have to divide by the length of hte sting.

    But why is trimming necessary?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube