Find exact word with spacing

Gylle

New Member
Joined
Apr 10, 2022
Messages
43
Office Version
  1. 365
Platform
  1. Windows
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. :)
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

saeedkk

Board Regular
Joined
Oct 16, 2014
Messages
54
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
Excel Formula:
=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

New Member
Joined
Apr 10, 2022
Messages
43
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 10, 2022
Messages
43
Office Version
  1. 365
Platform
  1. Windows
Donno where i upload excaple files

and atm i cant seem to get xl2bb to work :(
 

Gylle

New Member
Joined
Apr 10, 2022
Messages
43
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,577
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 16, 2014
Messages
54
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,
Excel Formula:
=IF(OR(EXACT(LOWER({"late","Urgent"}),LOWER(A1))),"V","P")
.

can you try this
 

Gylle

New Member
Joined
Apr 10, 2022
Messages
43
Office Version
  1. 365
Platform
  1. Windows
your note about Exact opened another option that you have,
Excel Formula:
=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.
Excel Formula:
=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

Board Regular
Joined
Oct 16, 2014
Messages
54
dear,
can you try the following:
Excel Formula:
=IF(C9="","",IF(OR(EXACT(LOWER(FindApr),LOWER($C9))),"P","V"))
 

Forum statistics

Threads
1,176,123
Messages
5,901,515
Members
434,899
Latest member
powerappsjoker99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top