Counting Multiple Occurrences of Text in a Cell

**bentleybob** · 04-30-2011, 02:44 PM

Looking for the most efficient way to determine the position of the LAST occurrence of specific text in a cell. So, if A1 contained AbcdefgAhijklmAnopqrstAuvwxyAz, and my criterion was "A", then I would like the formula in B1 to return 29.

It's annoying that neither FIND nor SEARCH has an "occurrence" option. I can build a compund formula using multiple uses of FIND, but it's hugely inefficient since I don't know what the maximum number of occurrences will be.

Thanks in advance.

**arthurbr** · 04-30-2011, 03:01 PM

Chip has this array formula to find last occurence of charcetr in B1 from string in A1

=MAX((MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)=B1)*ROW(INDIRECT("A1:A"&LEN(A1))))

EDIT can be found here

**Ron Coderre** · 04-30-2011, 03:08 PM

With
A1: containing text.....e.g. AbcdefgAhijklmAnopqrstAuvwxyAz
B1: a character to find...e.g. A

This regular formula returns the position of the last occurrence of that character

 
C1: =FIND("|",SUBSTITUTE(A1,"A","|",LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))))

In the above example, the formula returns: 29

Note: That formula is case-sensitive...so if A1 contains lower case a's, they would be ignored.
If you need it to NOT be case-sensitive...try this:

C1: =FIND("|",SUBSTITUTE(UPPER(A1),B1,"|",LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))))

Is that something you can work with?

**bentleybob** · 04-30-2011, 03:47 PM

Both formulas are very efficient. But Arthur: for some reason, I get a result of 1 (see the attached). Ron's formula gives me the correct result. Thanks very much to you both!

**arthurbr** · 05-01-2011, 04:04 AM

As I indicated it is an array formula, so, it should be committed with Ctrl+Shift+Enter

**bentleybob** · 05-02-2011, 01:44 PM

It didn't work for me the first time, but I see now that it does, in fact, work as an array formula. So sorry for having doubted your advice! And I also appreciate the link you attached as it answered another question I had, which was to be able to identify the nth occurrence. Thanks much.