Exact text search in a range of cells

[Reyaaz]

Hi, all.

I have a search textbox macro for cell B1, which checks a range of cells in sheet 2, and returns any cells which include the search term.
Sheet 2 has a number of paragraphs in the range of cells.

The search works, but how do I make it an exact search (not case-sensitive), for what is typed in B1.

Eg.
If someone types HR in B1, only cells which include an exact match are returned, and not any words (eg threat) which include the search term HR.
If someone types Perform in B1, only cells which include an exact match are returned, and not any words (eg performance) which include the search term perform.

This is the formula for the search so far:

=IF((LEN(B1))=0, "Product Search", FILTER('Product List'!A2:F152,ISNUMBER(SEARCH(B1,'Product List'!A2:A152))+ISNUMBER(SEARCH(B1,'Product List'!B2:B152))+ISNUMBER(SEARCH(B1,'Product List'!C2:C152))+ISNUMBER(SEARCH(B1, 'Product List'!D2:D152))+ISNUMBER(SEARCH(B1, 'Product List'!E2:E152))+ISNUMBER(SEARCH(B1, 'Product List'!F2:F152)),"No records found"))

Thanks

 

[jasonb75]

What those parts, " "& and &" " do is to add a space at the start and end of everything that the formula looks at, so for example it is looking for " HR " in each of the following
" HR "
" Threat "
" HR at the start "
" Ends with HR "
" Found HR in the middle "

By using spaces as part of the criteria the formula ignores longer words such as "Threat" because hr is not surrounded by spaces in this instance.

The spaces need to be added to the start and end of both the criteria (B1) and the reference table (Product List) in order to ensure accurate results. If you added the spaces to B1 but not to the rest then it would not find "HR at the start" or "Ends with HR".

Adding the spaces to the Product List, but not to B1 would do nothing and give you the same results as your original formula.

 

[Reyaaz]

What those parts, " "& and &" " do is to add a space at the start and end of everything that the formula looks at, so for example it is looking for " HR " in each of the following
" HR "
" Threat "
" HR at the start "
" Ends with HR "
" Found HR in the middle "

By using spaces as part of the criteria the formula ignores longer words such as "Threat" because hr is not surrounded by spaces in this instance.

The spaces need to be added to the start and end of both the criteria (B1) and the reference table (Product List) in order to ensure accurate results. If you added the spaces to B1 but not to the rest then it would not find "HR at the start" or "Ends with HR".

Adding the spaces to the Product List, but not to B1 would do nothing and give you the same results as your original formula.

Thanks for the explanation.

In relation to this same document, I have a different issue about website links not being clickable in the search results:

Web links in search results not clickable

Search results are listed on Sheet 1 (Search Results), and gathered from sheet 2 (Full Product List). However, cells which contain a web link in Full Product List, are not currently clickable in Search Results. Column 1 names are clickable in the full list on Full Product List. The user is...

www.mrexcel.com

 

[jasonb75]

I would suggest waiting for a couple of days to see if you get any useful replies to your other thread but I believe that you are going to need a completely different strategy. To the best of my knowledge a formula can not retrieve a clickable link from another cell. I have a feeling that your options will be to do everything in sheet 2 with advanced filters (so that the user can click on the original links in that sheet) or to use vba to copy everything over instead of formulas.

 

[Reyaaz]

I would suggest waiting for a couple of days to see if you get any useful replies to your other thread but I believe that you are going to need a completely different strategy. To the best of my knowledge a formula can not retrieve a clickable link from another cell. I have a feeling that your options will be to do everything in sheet 2 with advanced filters (so that the user can click on the original links in that sheet) or to use vba to copy everything over instead of formulas.

Ok.

I imagine that would be the same for another formatting issue with the same document.
Under FEATURES column, the first line of each feature is bold in the original Full Product List, but not in the Search Results.

 

[jasonb75]

I imagine that would be the same for another formatting issue with the same document.

You would be correct - formulas only return the actual value of the cell, not formatting or anything else associated with the source.