Exact text search in a range of cells

Reyaaz

New Member
Joined
Jan 16, 2023
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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
 
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.
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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:
 
Upvote 0
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.
 
Upvote 0
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. :(
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

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
Back
Top