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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: HELP PLEASE. Exact text search in a range of cells
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: HELP PLEASE. Exact text search in a range of cells
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Hi. I've only posted this once in this forum, right?
 
Upvote 0
Yes but you have also posted the same question on another site(s) & not included a link.
If you have posted this question on any sites other than here & ExcelForum then you need to supply links to those sites as well.
 
Upvote 0
Yes but you have also posted the same question on another site(s) & not included a link.
If you have posted this question on any sites other than here & ExcelForum then you need to supply links to those sites as well.
Sure. Is there an edit button for my original post, so I can add the links?
 
Upvote 0
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


 
Upvote 0
Because what you're effectively trying to do is filter multiple columns with multiple criteria, there is no simple way of doing it (or at least not one that comes to mind).

The only way that I can think of is to check each column four times as per the simplified example below, you would need to do the same for each column in your full range.

Book1
ABC
1HR
2HRHR
3ThreatHR at the start
4HR at the startEnds with HR
5Ends with HRFound HR in the middle
6Found HR in the middle
Product List
Cell Formulas
RangeFormula
C2:C5C2=IF((LEN(B1))=0, "Product Search", FILTER(A2:A6, (A2:A6=B1)+ IFERROR(SEARCH(B1&" ",A2:A6)=1,0)+ IFERROR(SEARCH(" "&B1,A2:A6)=(LEN(A2:A6)-LEN(B1)),0)+ ISNUMBER(SEARCH(" "&B1&" ",A2:A6)),"No records found"))
Dynamic array formulas.
 
Upvote 0
Actually, ignore what I said earlier. This method is less efficient but with only 150 rows, it shouldn't be awful.
Excel Formula:
=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"))
 
Upvote 0
Solution
Actually, ignore what I said earlier. This method is less efficient but with only 150 rows, it shouldn't be awful.
Excel Formula:
=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, Jason. This seems to work.

In layman's terms, what does " "&B1&" " effectively do?
Look for exact matches of B1, right?

Also, why the ampersands in this:
" "&'Product List'!A2:A152&" "

Thanks again for your help. Very appreciated. :)
 
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