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.

 

[saeedkk]

Hello,
if I understand the scenario here is that you have everything set but the only problem is that you have a set of words that you don't want to edit manually to add spaces, if this is the case, why don't you add

=concat(" ",Cell," ")

to make the the space addition dynamic.

I would be able to help you more if you upload the file or an example.

 

[Gylle]

I will try your suggestion. But where would i in the code i have would i put the CONCAT?




I have a list of words that is imported to another sheet; April
April has all the words that is the refrence list.

if i use this string:
=IF(COUNT(SEARCH({" late "," urgent "}," "&A1&" ")),"V","P")

that has the added spaces around the words. Yes i could use this string but my llist of words is sometimes with 100 words.
I dont want to sit and do that in that string.

so that setup i have works better to add or remove words from the list.
But it don't search for the exact word.

I have tried to change SEARCH with FIND and EXACT but theese are case sensitive
So i added dublicate words in the list file. Lower and upper case letters.
But that did not seem to work at all.

 

[Gylle]

Donno where i upload excaple files

and atm i cant seem to get xl2bb to work

 

[Gylle]

I tried to add CONCAT here

=IF(C9="";"";IF(MAX(IFERROR(concat(FindApr;" ";$C9;" ");0);0)>0;"P";"V"))

But no luck it return the same

 

[Peter_SSs]

and atm i cant seem to get xl2bb to work

What aspect of that are you having problems with? That is, at what step in the following do you run into problems?
XL2BB

 

[saeedkk]

I will try your suggestion. But where would i in the code i have would i put the CONCAT?




I have a list of words that is imported to another sheet; April
April has all the words that is the refrence list.

if i use this string:
=IF(COUNT(SEARCH({" late "," urgent "}," "&A1&" ")),"V","P")

that has the added spaces around the words. Yes i could use this string but my llist of words is sometimes with 100 words.
I dont want to sit and do that in that string.

so that setup i have works better to add or remove words from the list.
But it don't search for the exact word.

I have tried to change SEARCH with FIND and EXACT but theese are case sensitive
So i added dublicate words in the list file. Lower and upper case letters.
But that did not seem to work at all.

your note about Exact opened another option that you have,

=IF(OR(EXACT(LOWER({"late","Urgent"}),LOWER(A1))),"V","P")

.

can you try this

 

[Gylle]

your note about Exact opened another option that you have,

=IF(OR(EXACT(LOWER({"late","Urgent"}),LOWER(A1))),"V","P")

.

can you try this

Hi sorry for the late reply

I'm not sure how to work your surgestion into this line that I'm using.

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

This line connect to the name manager. That refers to a list with 10-100 words in it.

 

[saeedkk]

dear,
can you try the following:

=IF(C9="","",IF(OR(EXACT(LOWER(FindApr),LOWER($C9))),"P","V"))

 

[Gylle]

dear,
can you try the following:

=IF(C9="","",IF(OR(EXACT(LOWER(FindApr),LOWER($C9))),"P","V"))

It just returns false on everything.