Find exact partial string in range

[sh1msh0n]

Rookie here, please excuse my ignorance. Also, I am sure I would have found a relevant thread in the sea of knowledge here, but like in the Hitchhiker`s Guide, formulating the question (and thus researching it) is my bigger issue.

I am trying to set up a formula to find a string of text, and return a numerical value of the number of hits:

=COUNTIF('Copy Amenity VALUES Here'!A1:T200,"*$50*")

This works well, the problem is it only works when using wildcards, but then it also counts longer strings containing $50, i.e. $500, or $5000.

I have also tried:

=SUMPRODUCT(--ISNUMBER(SEARCH({"$50"}, 'Copy Amenity VALUES Here'!A1:T200))), which has the same issue.

Case sensitivity is not a problem. If I could e.g. specify the number of characters I am looking for, that could also solve the issue.

I am sure I must be missing some basic knowledge here - these functions are kind of useless if for instance "*hat*" gives a hit for the word "hate".

Any help is much appreciated.

[63falcondude]

Hello and welcome to the forum.

What about something like this:

=COUNTIF('Copy Amenity VALUES Here'!A1:T200,"*$50 *")

That will count the number of cells in the range A1:T200 that contain "$50 ".

This assumes that "$50" is somewhere in a sentence other than the end.

If "$50" can be at the end of a sentence (thus having a period instead of a space after it), try this:

=SUM(COUNTIF('Copy Amenity VALUES Here'!A1:T200,{"*$50 *","*$50.*")

If neither of these produce the expected result, consider uploading a small representative sample of your workbook along with the desired result.

[sh1msh0n]

Thank you - simply leaving a space solves my problem, and if later needed, I can work with your second version

Again, many thanks for the fast reply, you have made my day!

Have a fantastic weekend.

[63falcondude]

You're welcome, happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Enjoy your weekend.