00:00Here's one interview question that I was asked recently.
00:03How to do a word search using a formula?
00:05I'm not talking about pressing Ctrl F to do a word search.
00:09If you do this, you probably will not get this job.
00:13Let me show you how this can be done, and maybe I'll break down the formula as I go through.
00:18The first function you're going to use will be the infamous search function.
00:23The first argument in there will be the word that you want to search for.
00:27I'm going to be searching for a word bad.
00:29And over here, you're going to press F4 once to make the row and the column fix.
00:34And then the second argument on the search would be the text that you want to search the word in.
00:39After that, close parenthesis and hit enter.
00:42If the particular word, for example, the word bad is not found in this series of text, you'll get a
00:48value error.
00:49If the word is found, like for example, this sentence has the word bad in it, it will return the
00:55position.
00:55The first letter of position, the first letter of position where the word bad was found.
00:59You can see after the word really bad is over here, which is the eighth character on this sentence.
01:06With a search function, you can actually encapsulate with the is number function and close parenthesis.
01:11So what this does is that if the result of the search function is a number, you return true, else
01:17you return false.
01:18Now, if you were to nest this is function, another layer of nest again, with a if function like this.
01:25And if this statement is true, we're going to basically say, yeah, the word is found like this, word found.
01:33And if it's not true, of course, we're going to say not found like this, close parenthesis and hit enter.
01:40After that, you can apply the same formula for every row and then job done.
01:46Easy, wasn't it?
01:47Easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy,
01:47easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy,
01:47easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy,
01:47easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy,
01:47easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy,
01:47easy, easy, easy, easy, easy, easy, easy, easy, easy, easy, easy
