report number of occurrences in a text string

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?
 
J

JE McGimpsey

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
 

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

Locate text in range (multiple occurrences) and report other cell insame column? 10
Calculate Width of String, not Length? 'Em' not 'Kearning'? 2
How to type each letter of the alphabet automatically in a specific color in Word? 0
Extracting List of Occurrences 7
Text string searches 3
Remove certain characters from a text string 6
search text for substring and change text colour of substring 5
Number of Occurrences of a String Value within a String 4

Top