ExcelBanter - Check for a word

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Check for a word (https://www.excelbanter.com/excel-worksheet-functions/228703-check-word.html)

Kashyap

Check for a word

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

Jock

Check for a word

In E10 put:
=COUNTIF(B10:D10,"your word")
--
Traa Dy Liooar

Jock

"Kashyap" wrote:

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

Ken Johnson

Check for a word

On Apr 24, 12:36*am, Kashyap
wrote:
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

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

Ken Johnson

Glenn

Check for a word

Kashyap wrote:
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

Case sensitive:

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

Not case sensitive:

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

Glenn

Check for a word

Glenn wrote:
Kashyap wrote:
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

Case sensitive:

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

Not case sensitive:

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

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)

Kashyap

Check for a word

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.

"Glenn" wrote:

Glenn wrote:
Kashyap wrote:
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

Case sensitive:

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

Not case sensitive:

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

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)

Glenn

Check for a 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))

Kashyap wrote:
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.

"Glenn" wrote:

Glenn wrote:
Kashyap wrote:
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
Case sensitive:

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

Not case sensitive:

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

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)

Kashyap

Check for a word

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

"Glenn" wrote:

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))

Kashyap wrote:
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.

"Glenn" wrote:

Glenn wrote:
Kashyap wrote:
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
Case sensitive:

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

Not case sensitive:

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

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)

Kashyap

Check for a word

Now formula is not working at all..

"Glenn" wrote:

Kashyap wrote:
=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?

Remove the

" "&

and

&" "

from both parts of the formula.

Kashyap

Check for a word

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))

"Glenn" wrote:

What do you have for the "new formula"?

Kashyap wrote:
Now formula is not working at all..

"Glenn" wrote:

Kashyap wrote:
=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?

Remove the

" "&

and

&" "

from both parts of the formula.

Glenn

Check for a word

Kashyap wrote:
=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?

Remove the

" "&

and

&" "

from both parts of the formula.

Glenn

Check for a word

What do you have for the "new formula"?

Kashyap wrote:
Now formula is not working at all..

"Glenn" wrote:

Kashyap wrote:
=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?

Remove the

" "&

and

&" "

from both parts of the formula.

Kashyap

Check for a word

yes, I did enter both as array-formulas.. result from 2nd formula was '0'

Glenn

Check for a word

Looks right...did you enter both as array-formulas (commit with
CTRL+SHIFT+ENTER)? What result are you getting from the second one?

Kashyap wrote:
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))

"Glenn" wrote:

What do you have for the "new formula"?

Kashyap wrote:
Now formula is not working at all..

"Glenn" wrote:

Kashyap wrote:
=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?

Remove the

" "&

and

&" "

from both parts of the formula.

Glenn

Check for a word

Kashyap wrote:
yes, I did enter both as array-formulas.. result from 2nd formula was '0'

Without seeing your data, I can't say why you would be getting that result. If
you want, post your workbook on www.savefile.com and I'll take a look at it.

Kashyap

Check for a word

have uploaded file at http://www.savefile.com/files/2086874

"Glenn" wrote:

Kashyap wrote:
yes, I did enter both as array-formulas.. result from 2nd formula was '0'

Without seeing your data, I can't say why you would be getting that result. If
you want, post your workbook on www.savefile.com and I'll take a look at it.

Glenn

Check for a word

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))

Kashyap wrote:
have uploaded file at http://www.savefile.com/files/2086874

"Glenn" wrote:

Kashyap wrote:
yes, I did enter both as array-formulas.. result from 2nd formula was '0'

Without seeing your data, I can't say why you would be getting that result. If
you want, post your workbook on www.savefile.com and I'll take a look at it.

Kashyap

Check for a word

I'm getting error.. #N/A

Glenn

Check for a word

Kashyap wrote:
I'm getting error.. #N/A

Yes, if no match is found.

All times are GMT +1. The time now is 04:49 PM.