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

Gylle

New Member
Joined
Apr 10, 2022
Messages
43
Office Version
  1. 365
Platform
  1. Windows
MainWB.xlsx
ABCDEFG
1scarlotteløgTrue<- should be false
2løgTrue<- should be true
3ramsløgTrue<- should be false
4gulerodTrue<- should be true
5 
6 
7
8
9
10
11
12
Ark1
Cell Formulas
RangeFormula
E1:E3E1=IF(C1="","",IF(MAX(IFERROR(concat(FindApr," ",$C1," "),0),0)>0,"False","True"))
E4:E6E4=IF(C4="","",IF(MAX(IFERROR(SEARCH( FindApr,$C4),0),0)>0,"False","True"))


Name manager:

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

April.xlsx
ABCD
1April
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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

saeedkk

Board Regular
Joined
Oct 16, 2014
Messages
54
MainWB.xlsx
ABCDEFG
1scarlotteløgTrue<- should be false
2løgTrue<- should be true
3ramsløgTrue<- should be false
4gulerodTrue<- should be true
5 
6 
7
8
9
10
11
12
Ark1
Cell Formulas
RangeFormula
E1:E3E1=IF(C1="","",IF(MAX(IFERROR(concat(FindApr," ",$C1," "),0),0)>0,"False","True"))
E4:E6E4=IF(C4="","",IF(MAX(IFERROR(SEARCH( FindApr,$C4),0),0)>0,"False","True"))


Name manager:

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

April.xlsx
ABCD
1April
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

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

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,587
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
=IF(COUNT(SEARCH(" "&TRIM(April!A$1:A$1000)&" "," "&TRIM(C1)&" ")),"P","V")
 
Solution

Gylle

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,587
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Forum statistics

Threads
1,176,238
Messages
5,902,089
Members
434,939
Latest member
MrDrock

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