Find exact word with spacing

[Gylle]

Hi all


i'm using this to search for words

formular string:
=IF(C9="";"";IF(MAX(IFERROR(SEARCH(FindApr;$C9);0);0)>0;"P";"V"))

name manager string:
=OFFSET(April!$A$1;1;0;OFFSET(TRUE;INDEX(ISBLANK(April!$A:$A);0;0);0)-2)

Now it works fine, but it don't produce the outcome i want.
Problem is that it find words inside words.
example: if i want to search for the word "late" it finds it, but it also finds in "template"
as I understand I can use a formular with the words inside the string and then use sapcing like " late "
but i have to many words to that type therefor i use a danynamic sheet insted.

I just cant figure out where i would put the spacing to find the exact word and any other

hope i make sense.

 

[Gylle]

MainWB.xlsx
	A	B	C	D	E	F	G
1			scarlotteløg		True	<- should be false
2			løg		True	<- should be true
3			ramsløg		True	<- should be false
4			gulerod		True	<- should be true
5
6
7
8
9
10
11
12
Ark1

Cell Formulas
Range	Formula
E1:E3	E1	=IF(C1="","",IF(MAX(IFERROR(concat(FindApr," ",$C1," "),0),0)>0,"False","True"))
E4:E6	E4	=IF(C4="","",IF(MAX(IFERROR(SEARCH( FindApr,$C4),0),0)>0,"False","True"))

Name manager:

=offset(April!$A$1;1;0;match(true;index(isblank(April!$A:$A);0;0);0)-2)

April.xlsx
	A	B	C	D
1	April
2	Agurk
3	Champignon
4	Enoki
5	Gulerod
6	Kartoffel
7	Kejserhat
8	Løg
9	Portobello
10	Rødbede
11	Rødløg
12	****aki
13	Spæd salat
Ark1

It should search for the excat word, not a word in a word.

 

[saeedkk]

MainWB.xlsx
	A	B	C	D	E	F	G
1			scarlotteløg		True	<- should be false
2			løg		True	<- should be true
3			ramsløg		True	<- should be false
4			gulerod		True	<- should be true
5
6
7
8
9
10
11
12
Ark1

Cell Formulas
Range	Formula
E1:E3	E1	=IF(C1="","",IF(MAX(IFERROR(concat(FindApr," ",$C1," "),0),0)>0,"False","True"))
E4:E6	E4	=IF(C4="","",IF(MAX(IFERROR(SEARCH( FindApr,$C4),0),0)>0,"False","True"))

Name manager:

=offset(April!$A$1;1;0;match(true;index(isblank(April!$A:$A);0;0);0)-2)

April.xlsx
	A	B	C	D
1	April
2	Agurk
3	Champignon
4	Enoki
5	Gulerod
6	Kartoffel
7	Kejserhat
8	Løg
9	Portobello
10	Rødbede
11	Rødløg
12	****aki
13	Spæd salat
Ark1

It should search for the excat word, not a word in a word.

please find the excel at the following link, the just open, and press download from the file menu Column G has the formula, I added to it trim( to remove spaces from the sides since we are using.

 

[Gylle]

please find the excel at the following link, the just open, and press download from the file menu Column G has the formula, I added to it trim( to remove spaces from the sides since we are using.

Thank you so much

It works ....
If i create a new file it works. Your file works.
However if i copy the code into my excisting workbook it just returns "not found" it's kinda weird. Even if i delete the importet data and try to import again changing names etc. for the sheet. Still "not found" I cant see where it goes wrong so i guess i have some digging to figure out.

But the code works just as intended. So Thank you for the help.

 

[Gylle]

please find the excel at the following link, the just open, and press download from the file menu Column G has the formula, I added to it trim( to remove spaces from the sides since we are using.

I figured out why is it not working ... It only search for the first word. That means it does not run through the whole sentence and look for words.

The idea ws for the code to search the whole sentence for a spesific word.

 

[Peter_SSs]

Perhaps I am not understanding just what your data is like or your requirement

The idea ws for the code to search the whole sentence for a spesific word.

Your sample data has no sentences!

If the following does not do what you want, please post a small set of representative sample data and the expected results with XL2BB.

Try

=IF(COUNT(SEARCH(" "&TRIM(April!A$1:A$1000)&" "," "&TRIM(C1)&" ")),"P","V")

 

[Gylle]

Perhaps I am not understanding just what your data is like or your requirement

Your sample data has no sentences!

If the following does not do what you want, please post a small set of representative sample data and the expected results with XL2BB.

Try

=IF(COUNT(SEARCH(" "&TRIM(April!A$1:A$1000)&" "," "&TRIM(C1)&" ")),"P","V")

Yes your right I think i did not explain it corectly ... I appologize.

But the code worked as I wanted to.

It now searches the whole sentence and find the specific word from the list ... Perfect.. Thanks you for the help.

Thank you all for your understanding and help.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.