How to use SEARCH formula to search only completed words? [SOLVED]

[vigneshjan24]

I want to get a result as if A1 contains the word "pen", in B1 it should result as "pen" otherwise it should be "NO". So this is the formula i used in B1

=IF(ISNUMBER(SEARCH("pen",A1)),"pen","NO")


Below are the results:

Left side is input and right side is result


1) Black pen = pen

2) Blue pen = pen

3) Rubber = NO

4) pencil = pen

5) Scale = NO

6) Red pen = pen


^in the above results you may note that the first three and last two results came correctly as desired. But in fourth the word "pen" in "pencil" have been taken by formula. But it should not.


In my usage I will not meet this issue but i want to invent this thing for my knowledge.

Pls sort this out.

 

[NARAYANK991]

Hi Vignesh ,


The standard technique in this kind of a search is this :


=IF(ISNUMBER(SEARCH("pen ",A1&" ")),"pen","NO")


Narayan

 

[vigneshjan24]

Wow, i wonder why i didnt think to use "pen ".

First of all thanks. This is my first question in this site and got answer in ten minutes.


But i couldnt understand that in the SEARCH syntax you have given as A1&" "

How it works?

 

[NARAYANK991]

Hi Vignesh ,


Using just "pen " instead of "pen" will work where ever the word "pen" occurs in the middle of a sentence or phrase , since in any sentence or phrase , words are separated from one another by a space character ; what about when the word is the last word in a sentence or phrase , as in the sentence Give me a pen ?


To take care of this possibility , we add a space to the input string which is being searched , so that using "pen " will work in this case also.


Narayan

 

[vigneshjan24]

Dear narayan,


Actually i understood the purpose of adding space to input string that to search in both the cases that the desired word in middle as well as ended at last.


But my doubt is how adding of that space to the input string works in the above case?

How it ignores the space if the word ended with pen (without space)?

Sorry for my poor english, I hope you understand my question.

 

[NARAYANK991]

Hi Vignesh ,


I think there is some confusion ; suppose we assume that the text we are looking for is A ( one single word e.g. pen ) ; suppose we assume that the text within which we are looking for this word A is a string of words B ( as in the sentence Give me a pen ) ; note that this should end with a period ".".


Now , if we concatenate the space character to A , and look for it in B , we will not find it , since B contains the word A without the added space , since B's last word is A.


However , the word A is existing in the string B.


Thus , if our technique has to work even in such a case , we need to add the space character to the string B also.


Try it with the example Give me a pen , using both the formulae , and you will see the difference.


Narayan

 

[vigneshjan24]

Dear Narayan,


I again telling you i already understood why we need to add the space in the string also.


My question is how it works? How it ignores the space if the word is the last one. Because by adding space with string it will result as A1$" " >> A1space.

How it working in the search?

 

[NARAYANK991]

Hi Vignesh ,


There is some deep-rooted misunderstanding at work here !


The formula does not ignore the space if the word is the last one ; if I have to search for pen[space] in a string like Give me a pen where the word pen occurs at the end , I will not find it , unless I add a space to the end of the string Give me a pen also. So if the looked for word , pen in this case , is the last word in the sentence , it is absolutely essential that if we add a space to the end of the word , as in pen[space] , we add the space to the end of the sentence also.


If the word pen occurs in the middle of a sentence or phrase , as in This pen does not write , a space follows the word pen anyway , so the added space at the end of the sentence does not matter.


Narayan

 

[vigneshjan24]

Dear Narayan,


Now i clearly understood that adding space after string will add space to the value of the string linked to it also. So while searching with or without space it matches the criteria.


Thanks for your kind responses with humble patient in explaining my doubt.