SEARCH function in Excel

Excel TEXT functions return a number in a specified number format

How to extract text between parentheses in Excel

To extract text between parentheses, braces, brackets, etc. you can use a formula based on the MID function, with help from SEARCH function. Formula =MID(text,SEARCH(“(“,text)+1,SEARCH(“)”,text)-SEARCH(“(“,text)-1) Explanation In the example shown, the formula in C5 is: =MID(B5,SEARCH(“(“,B5)+1,SEARCH(“)”,B5)-SEARCH(“(“,B5)-1)+0 How this formula works The foundation of this formula is the MID function, which extracts a specific number of… read more »

How to extract substring in Excel

To extract a substring with an Excel formula, you can use the MID function. Note: In this example, we are calculating the end position in order to extract a substring with a literal start and end position. However, if you know the number of characters to extract, you can just plug in that number directly.  Formula… read more »

How to check cell that contains specific text in Excel

To check if a cell contains specific text, you can use the SEARCH function together with the ISNUMBER function. In the generic version, substring is the specific text you are looking for, and text represents text in the cell you are testing. Formula =ISNUMBER(SEARCH(substring,text)) Explanation  In the example shown, the formula in D5 is: =ISNUMBER(SEARCH(C5,B5)) This formula returns TRUE if… read more »

How to check if cell contains some words but not others in Excel

To test a cell to see if contains certain words but not others, you can use an array formula based on the COUNT and SEARCH functions, wrapped in the AND function. Formula {=AND(COUNT(SEARCH(inc,A1))>0,COUNT(SEARCH(exc,A1))=0)} Explanation In the example shown, the formula in C5 is: {=AND(COUNT(SEARCH(inc,B5))>0,COUNT(SEARCH(exc,B5))=0)} This formula returns TRUE when B5 contains any of the words… read more »

How to check cell that contains one of many with exclusions in Excel

To test a cell for one of many strings, while excluding others, you can use a formula based on the SEARCH, ISNUMBER, and SUMPRODUCT functions. Formula =(SUMPRODUCT(–ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(–ISNUMBER(SEARCH(exclude,A1)))=0) Note: this formula returns either 1 or zero, which are handled like TRUE and FALSE in formulas, conditional formatting, or data validation. Explanation In the example shown the… read more »

How to check if cell contains one of many things in Excel

This tutorial shows how to check if cell contains one of many things in Excel using example below: If you want to test a cell to see if it contains one of several things, you can do so with a formula that uses the SEARCH function, with help from the ISNUMBER and SUMPRODUCT functions. Formula =SUMPRODUCT(–ISNUMBER(SEARCH(things,A1)))>0… read more »

How to check if cell contains all of many things in Excel

If you want to test a cell to see if it contains all items in a list, you can do so with a formula that uses the SEARCH function, with help from the ISNUMBER, SUMPRODUCT, and COUNTA functions. Case study: Let’s say you have a list of text strings in the range B5:B8, and you want… read more »

How to check cell contains which things in Excel

If you have a list of things (words, substrings, etc) and want to find out which of these things appear in a cell, you can build a simple table and use a formula based on the SEARCH function. Setup Suppose you have a cells that contain text that mentions various colors, and you want to… read more »

How to count keywords in a range of cell

To count the number of specific words or keywords that appear in a given cell, you can use a formula based on the SEARCH, ISNUMBER, and SUMPRODUCT functions.  Formula =SUMPRODUCT(–ISNUMBER(SEARCH(keywords,A1))) Note: if a keyword appears more than once in a given cell, it will only be counted once. In other words, the formula only counts… read more »

Excel Data validation must not contain

Set criteria to reject specific data in Excel To disallow input that contains one of many things, you can use a custom data validation rule based the SEARCH function. Formula =SUMPRODUCT(–ISNUMBER(SEARCH(list,A1)))=0 Explanation In the example shown above, the data validation applied to B5:B11 is: =SUMPRODUCT(–ISNUMBER(SEARCH(list,B5)))=0 Note:  Cell references in data validation formulas are relative to… read more »

Sidebar