report number of occurrences in a text string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

skyboy_psu - 13 Jan 2008 05:01 GMT

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?

Reply to this Message

JE McGimpsey - 13 Jan 2008 05:31 GMT

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

In article
<81db02c7-6746-46c6-b609-a1ee62c4574b@e6g2000prf.googlegroups.com>,

> Hello all,
>
[quoted text clipped - 3 lines]
> letters, how many times does the letter A occur, or the pair BC, and
> so on?

Reply to this Message