Forum Discussion

Copper Contributor

Jan 16, 2023

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

excel

Formulas and Functions

Macros and VBA

office 365

OliverScheurich

Gold Contributor

Jan 16, 2023

Reyaaz

=IF(LEN(B1)=0,"Product Search",FILTER(D1:G5,N(ISNUMBER(SEARCH(","&B1&",",","&SUBSTITUTE(D1:D5," ",",")&", ")))+N(ISNUMBER(SEARCH(","&B1&",",","&SUBSTITUTE(E1:E5," ",",")&", ")))+N(ISNUMBER(SEARCH(","&B1&",",","&SUBSTITUTE(F1:F5," ",",")&", ")))+N(ISNUMBER(SEARCH(","&B1&",",","&SUBSTITUTE(G1:G5," ",",")&",")))))

If you want to identify "hr" in a cell with "our hr department" you can try this formula.

Reyaaz

Copper Contributor

Jan 16, 2023

Thanks for the response, OliverScheurich.

This seems to work:

https://www.mrexcel.com/board/threads/exact-text-search-in-a-range-of-cells.1227086/#post-6003752