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

 

[Fluff]

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.

 

[Reyaaz]

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?

 

[Fluff]

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.

 

[Reyaaz]

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?

 

[Fluff]

Just post a reply with the links.

 

[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

Exact text search in a range of cells

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

techcommunity.microsoft.com

Exact text search in a range of cells

Exact text search in a range of cells Excel

www.msofficeforums.com

https://www.reddit.com/r/excel/comments/10de8qe

 

[jasonb75]

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
	A	B	C
1		HR
2	HR		HR
3	Threat		HR at the start
4	HR at the start		Ends with HR
5	Ends with HR		Found HR in the middle
6	Found HR in the middle
Product List

Cell Formulas
Range	Formula
C2:C5	C2	=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.

 

[jasonb75]

Actually, ignore what I said earlier. This method is less efficient but with only 150 rows, it shouldn't be awful.

=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"))

 

[Reyaaz]

Actually, ignore what I said earlier. This method is less efficient but with only 150 rows, it shouldn't be awful.

=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.