Find the frequency and location of a recurring word in a 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 above i want to find the total occurrences and the location of the word 'Humpty'.

Expected Result:

Occurances  location
1            1  
2            30

Is there any way in which I can know the above?

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

Thanks!

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:

Find the target (Humpty) in the sample text (Humpty dumpty etc..) and start searching from the next position to the last found. If no more target text is found, return not found. The IFERROR(VALUE(B4),0)) prevents an error for the first search which should always start from position 1, but needs to ignore the fact it is checking the location label.

Here's a screenshot of the implementation: