| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Check for a word
yes, I did enter both as array-formulas.. result from 2nd formula was '0'
|
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Check for a word
I'm getting error.. #N/A
|
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Check for a word
Kashyap wrote:
I'm getting error.. #N/A Yes, if no match is found. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Check if cells contain the word "Thailand", return "TRUE" | Excel Worksheet Functions | |||
| Paste Word Check Box Form Field Link into Excel | Excel Discussion (Misc queries) | |||
| excel check boxes moving when excel selected cells pasted in word | Excel Discussion (Misc queries) | |||
| I need a bank check template for word or excel | Excel Discussion (Misc queries) | |||
| Auto spell check as in word | Excel Discussion (Misc queries) | |||
Linear Mode
