report number of occurrences in a text string

  • Thread starter Thread starter skyboy_psu
  • Start date Start date
S

skyboy_psu

Hello all,

I want to report the number of times a particular item occurs in a
text string that is in a single cell.
For a simple example, If I have the string ABCABCABC....with 100
letters, how many times does the letter A occur, or the pair BC, and
so on?
 
General way:

=(LEN(A1) - LEN(SUBSTITUTE(A1,"text","")))/LEN("text")

Specifically:

=LEN(A1) - LEN(SUBSTITUTE(A1,"A",""))

and

=(LEN(A1) - LEN(SUBSTITUTE(A1,"BC","")))/2
 
Back
Top