Please help on COUNTIF Partial text, partial text/words also contained in other texts

Hi,

I am trying to use the COUNTIF formula teamed with wildcards for partial text matches. I have managed to get the formula working ok however have discovered one serious flaw.

When the partial text already contains that part of the word.

I.E. searching for the suburb "austral" however this is then picked up whenever the externally inputed word "australia" is used.
I.E. searching for the suburb "tweed heads" when the customer has input "tweed heads south".

How do i get excel to count on the full version of the word, to be found in an assortment of customer input text?

I have attached a quick example.

I am using data from a website enquiry feed, then saving it into my excel spreadsheet - so short of manually removing unnecessary information i.e. Australia whenever it pops up, I still don't have a way to solve the Tweed Heads/Tweed Heads South issue.

Thanks in advance!

Using your posted workbook.... I "think" this regular formula (copied down) does what you need:

Please Login or Register  to view this content.

Is that something you can work with?