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:
=NOT(ISERROR(FIND(“FOO”,”FOOBAR”)))
=TRUE
But then had some issues with case that screwed things up:
=NOT(ISERROR(FIND(“Foo”,”FOOBAR”)))
=FALSE
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:
=SEACH(“FOO”,”FOOBAR”)
=#NAME?
Whoops…that’s not how you spell SEARCH! Why didn’t you tell me Excel? Oh…because of this:
=ISERROR(SEACH(“FOO”,”FOOBAR”))
=TRUE
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.”)))
=TRUE
