NOT learning from my Errors

I was writing some formulas today that need to return TRUE if a search term appears in a cell, and FALSE otherwise. For instance, I wanted to know if FOO appeared in FOOBAR or not.

Originally I was using FIND, along these lines:

But then had some issues with case that screwed things up:

Given that for my purposes FOO was as good as Foo, I decided to replace the FIND function in this with the SEARCH function, on account of SEARCH being an insensitive bastard. (Can I say that here? No? Oops…sorry!).

So I manually edited the formula, and replaced FIND with SEACH. Then cut and pasted that formula in lots of other places. Then did lots of analysis with the output. Without learning from the error of my ways:

Whoops…that’s not how you spell SEARCH! Why didn’t you tell me Excel? Oh…because of this:

Which don’t mean there ain’t no FOO (or Foo) to be found. Rather it means “Yes, I am in fact returning an error – thank you for asking – because I have no idea what this damn SEACH does.”

Murphy’s law: I never noticed that I’d screwed it up until right at the very end of my days work.

Still, not noticing till the very end is better than not noticing.

Or as Excel would put it:
=NOT(ISERROR(SEARCH(“not noticing”,”not noticing till the end is better than not noticing.”)))

4 thoughts on “NOT learning from my Errors

  1. I always type functions in lower case, if it is wrong then it doesn’t get upshifted.

  2. I never trust my memory about what is case sensitive and what isn’t. If I want to ignore case, I usually transform the two items being compared with LOWER() in the worksheet or with LCase$() in VBA. Which I would type in as “lower” and “lcase”, because like Bob, I always type the names in lower case, to make sure the case changes when I hit Enter.

  3. I perform similar searches quite often, I have become fond of:


    You can complex-ify this method, as well, by adding a condition.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.