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:
=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

4 Comments

  1. Bob Phillips says:

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

  2. Jon Peltier says:

    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. BSCowboy says:

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

    =isnumber(search(“foo”,”foobar”))

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

    =isnumber(iferror(search(“bal”,”foobar”),search(“foo”,”football”))

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: