Saturday, March 5, 2011


Someone recently asked me to help with an Excel problem he was trying to solve. I don't think I solved his problem, but I added a neat new trick to my bag.

The question (not necessarily the question asked) I found a solution to was this: is it possible to extract one precise word from a string no matter where it is in the string? We've already seen that it's possible to extract words or numbers based on their position in a string using mid, left and right.

The answer: the SEARCH Function!

You can read above to see what it does.

To get it to actually return the word, you have to nest this with an ISERROR


The formula itself is:

In english, if the SEARCH function does not find the word "credit", return the value in A2, otherwise, return the word "Credit"

Here it is in action: