Count How Many Times a Character Appears in a Cell - Excel Formula

Count how many times a character appears within a cell using an Excel formula. This same formula can also count the occurrences of a specific word, or any character in a cell.

In this video tutorial, we write an Excel formula to count the number of times an asterisk (*) occurs within a cell. The formula would work for any character.

The formula we use consists of the LEN and SUBSTITUTE functions. Two incredibly useful text functions of Excel.

Find more great free tutorials at;

Connect with us!

[mhmmd]

Thanks, your solution has helped me a lot

[Quijanos]

This is one of the most useful videos. Not only is it useful but when combined with the if function, the potency of trying to determine where or at what level a row of information can be found is resource saving. Thank you so much sir. You are fantastic and have saved me endless hours in cutting and pasting. Have a phenomenal day.

[elizabethkagansky]

Thank you! I tweaked it a little because I was looking for a specific word. I just divided the entire formula by the number of characters in my word and WALA! Thanks so much!

[shihbo]

i have travelled the internet looking for this, thanks you very much <3

[ajaykushwaha-jemw]

very very help full, you have always saved me.

[markmalatesta]

Just what I was looking for. Thanks for the help.

[billkioko]

Thankyou so much. This was very helpful

[JacksonGilbert]

Thanks. This logic helped me too.
I was also looking for as solution to find the number of text occurrences within a cell.

And worked out below formula based on your concept.
=IF(LEN(TRIM(B2))=0, 0, LEN(TRIM(B2))-LEN(SUBSTITUTE(B2, A7, ""))+1)/len(A7)

{ My full text to analyse is in cell B2.
And my character text to check the number of occurrences is updated in A7.
- So in short, what ever substring text I entered in A7 will be checked within B2 & the number of occurrences is reported.

[zombiemouse]

Just saved me like 4 hours of work, thanks!

[kennethcesmhk]

Great video, almost exactly what I was looking for. I have used your character in a range version to identify how many characters are in the range, now I would like to update this number by filtering the range. is there any way of doing this?

[weiliaw]

Hi, may I have your advise if I there is 2 sets of data which column A is combines data separate with comma and the value is 1, 2, 3 and column B is another cell with a combined date 7, 8, 9 . How can I come up with the formula to get the results total value of the first number show in column A to multiply the first number in column B plus the 2nd number in column A multiply with 2nd number in column B and so on for example like (1*7)+(2*8)+(3*9)=50? Thanks.

[trebligoncemore]

Thank you... I learn something new... :)

[subhashisbasak]

Does this formula works on MS EXCEL 2003?

[ckhara]

Thanks for these tips. How to handle CAPS and small characters such as "S" and "s" in A4, if I wants to count character "s"

[rpsingh]

Really helpful. Thanks you lot sir.
Also can you please help me to find out the characters from a range that are repeated.

[nocarpel]

This is exactly what I needed! Could you do this with a range, or does the formula change for that?

[claybwagner]

Amazing. thank you. I have a spreadsheet where I need to count the number of text comments in a range. Problem is some cells contain 2 separate lines (Alt + Enter). That only returns 1 using COUNTIF, but if I have them use bullet points for each line, it would work. THANK YOU!!!!

[RockYourMic]

This video is AWESOME!!! Can you point me to a video that shows how to find the @ sign in cells T9:AB9?

[mihailverdes]

Hi, Thank you for a such support. Like in the example shown can you count how many times number 1 or 7 appears in the cells from A column?.

[Savere]

How do you count how many characters of a particular letter or number in a cell?