Check for a word

  • Thread starter Thread starter Kashyap
  • Start date Start date
K

Kashyap

I was try on a formula something like, if 1 of the words in b10=d10 then it
should give output as 1 in E10
 
I was try on a formula something like, if 1 of the words in b10=d10 then it
should give output as 1 in E10
Click to expand...

=IF(COUNTIF(B10,"*"&D10&"*")=1,1,"")

Ken Johnson
 
Kashyap said:
I was try on a formula something like, if 1 of the words in b10=d10 then it
should give output as 1 in E10
Click to expand...

Case sensitive:

=IF(ISERROR(FIND(B10,D10)),0,1)

Not case sensitive:

=IF(ISERROR(SEARCH(B10,D10)),0,1)
 
Glenn said:
Case sensitive:

=IF(ISERROR(FIND(B10,D10)),0,1)

Not case sensitive:

=IF(ISERROR(SEARCH(B10,D10)),0,1)
Click to expand...


Actually, I think i reversed your needs and didn't allow for only whole word
matches:

=IF(ISERROR(FIND(" "&D10&" "," "&B10&" ")),0,1)

=IF(ISERROR(SEARCH(" "&D10&" "," "&B10&" ")),0,1)
 
I tried implimenting this for a range.. But..

That is Say I have words list from H1:H25 and sentences from B1:B200

Now, in ColC (C1:C200) I need a formula which will search for a word (which
is in H1:H25) in B1:B200 and enter that particular word.
 
The following array formula (commit with CTRL+SHIFT+ENTER) in C1 and copied down
to C200 should work if only one word will be found in each sentence:

=INDEX($H$1:$H$25,MATCH(0,--ISERROR(FIND(" "&$H$1:$H$25&" "," "&B1&" ")),0))
 
=IF(ISERROR(SEARCH(B10,D10)),0,1) - This formula will search a word even
though something else is prefixed/suffixed to the word

where as

=INDEX($H$1:$H$25,MATCH(0,--ISERROR(search(" "&$H$1:$H$25&" "," "&B1&"
")),0))

- This will not serch if something else is prefixed/suffixed to the word.

For Eg., - Take a word Kashyap

if in a sentence word is Kashyap# then 1st formula detects where as 2nd
doesn't..

How to make 2nd formula detect words as 1st?
 
I'm getting required result from
=INDEX(DB!$A$1:$A$4003,MATCH(0,--ISERROR(SEARCH(" "&DB!$A$1:$A$4003&" ","
"&B8&" ")),0))

but not from
=INDEX(DB!$A$1:$A$4003,MATCH(0,--ISERROR(SEARCH(DB!$A$1:$A$4003,B8)),0))
 
Kashyap said:
=IF(ISERROR(SEARCH(B10,D10)),0,1) - This formula will search a word even
though something else is prefixed/suffixed to the word

where as

=INDEX($H$1:$H$25,MATCH(0,--ISERROR(search(" "&$H$1:$H$25&" "," "&B1&"
")),0))

- This will not serch if something else is prefixed/suffixed to the word.

For Eg., - Take a word Kashyap

if in a sentence word is Kashyap# then 1st formula detects where as 2nd
doesn't..

How to make 2nd formula detect words as 1st?
Click to expand...


Remove the

" "&

and

&" "

from both parts of the formula.
 
Looks right...did you enter both as array-formulas (commit with
CTRL+SHIFT+ENTER)? What result are you getting from the second one?
 
It is the blank lines within DB!A1:A4003 that are causing the problem. Either
remove them, replace them with non-matching data (I used "blank line") or adjust
the formula to something like this (array-entered):

=INDEX(DB!$A$1:$A$4003,MATCH(0,--ISERROR(SEARCH(IF(DB!$A$1:$A$4003="",
"blank line",DB!$A$1:$A$4003),A2)),0))
 
Back
Top