Find frequency and location of repeating word in cell in excel

Question

How can I find () the location of the Nth occurrence of a word in a sentence in a cell?

Example:

Humpty Dumpty sat on a wall. Humpty had a great fall.

In the section above, I want to find common occurrences and the location of the word "Humpty".

Expected Result:

Occurances  location
1            1  
2            30

Is there a way I can know above?

I want to use the location of the second occurrence and calculate further if there are more than two occurrences of the target word.

Thank!

Answer 1

Without using vba you can use this formula:

=IFERROR(FIND($B$2,$B$1,1+IFERROR(VALUE(B4),0)),"not found")

Which basically says:

(Humpty) (Humpty dumpty etc..) . , not found. IFERROR(VALUE(B4),0)) , 1, location.

:

Answer 2

:

=IFERROR(FIND("@@@",SUBSTITUTE($A$1,$B$4,"@@@",$A6)),"")

. :