Formula request

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to find a formula that will tell me the frequency of a character appearing within a range of text.
For instance, A1 has JUICY and A2 has JUMBO. If I want to find the number of times J appears, it's 2 times

Thanks you.
 
One way:
=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"J","")))
(watch out =substitute is case sensitive, so it won't count lower case j's.


If you want to count both j's and J's:
=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),"J","")))
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Instances of a String within a Matrix 3
Beginer's problemBeginner's problem 6
Take me to the number 1
Delimit using a formula 2 4
Find some text but not others 1
Counting Specific Character(s) In A Range? 10
time formulas 2
Searching alfanumaric cell contents and returning with a numaric v 21

Back
Top