How to Search for Text within Text with Case Insensitive AND No Partial Searches?

I looked online but couldn't find a solution. I am looking for the best of both worlds from the Find and Search function.

Find: It gives me exact match and is case sensitive
Search: It gives me partial searches and is case insensitive

I want to search for results that are exact matches and case insensitive.

Here is the search formula I am currently using:

=IF(ISNUMBER(SEARCH("excel",$A2485,1)),1,0)

UPDATE:

I am a bit wrong in my conclusions. I see that for the Find function, if I search for the word "Excel" with the E capitalized, the word is "found" in the word Excellent, which I don't want.

Sample Title: 45 Ways to Excellent Life

I want the word "Excel" to be found only if the entire / whole word Excel is in that string / line / cell, regardless if I am searching for (if the formula has this) Excel, excel, EXCel, and regardless if the text being searched has Excel, excel, EXCell, EXCellent, etc.

Sample Title: 45 Ways to Excellent Life : Result should be not found
Sample Title: 45 Ways to excellent Life : Result should be not found
Sample Title: I love Excel, math and science : Result should be found
Sample Title: I love excel, math and science : Result should be found

Sorry if I am being a bit dim here, but haven't you answered your own question? Use SEARCH in one set of results and FIND in the other, surely? Or am I missing something?

=IF(ISNUMBER(MATCH("excel",$A2485,0)),1,0)

shoudl do that...

I have updated my original post to clarify exactly what I am hoping to accomplish.

See post #4.

Mmmm. Not convinced. this might be a morerobust alternative:

Please Login or Register  to view this content.

-FindEngWord(A1,"excel")

Thanks. I will test this some other time. I figured it wasn't the best, as it would have to account for every single punctuation / symbol that exists. Right now it didn't pickup the word I was searching for because it had a colon after it, which the formula didn't have.

Certainly you'll need one more SUBSTITUTE, to cope with:

= yadda yadda yadda excel.

Rose - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

Perhaps regular expressions would help

Please Login or Register  to view this content.

Hi all- How about this?:

SEARCH allows wildcards (?, *). In normal sentence structure, a word terminates with a space, or with a single punctuation mark followed by a space. Check for both. Add a space to the end of the search_in text to catch words at the end of a string.

Formula:

Please Login or Register  to view this content.

PS NOTE- The above verifies the END of the word. To verify the BEGINNING too, use this:

Formula:

Please Login or Register  to view this content.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

OR TRY
=IFERROR(IF(AND(FIND("Excel",A1),OR(CODE(MID(A1,SEARCH("Excel",A1)+5,1))<65,CODE(MID(A1,SEARCH("Excel",A1)+5,1))>122),OR(CODE(MID(" "&A1,SEARCH("Excel"," "&A1)-1,1))<65,CODE(MID(" "&A1,SEARCH("Excel"," "&A1)-1,1))>122)),"Found","Not Found"),"Not Found")

Please Login or Register  to view this content.

sorry ignore post no 15
try

Please Login or Register  to view this content.