Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Tag: SEARCH function

SEARCH, SEARCHB functions: Description, Usage, Syntax, Examples and Explanation

What are SEARCH, SEARCHB functions in Excel? SEARCH, SEARCHB functions are TEXT functions in Microsoft Excel that locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string. Syntax of SEARCH, SEARCHB functions SEARCH(find_text,within_text,[start_num]) SEARCHB(find_text,within_text,[start_num]) The SEARCH and SEARCHB functions have the following…

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 characters from text,…

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 =MID(A1,start,end-start+1) Explanation In the example…

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 the substring is found, and…

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 in the named range inc and none…

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 formula in C5 is: =(SUMPRODUCT(–ISNUMBER(SEARCH(include,B5)))>0)…

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 Explanation Context Let’s say you…

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 to find out if these…

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 figure out which colors are…

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 instances of different keywords. Explanation…

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 the upper left cell in…

Highlight rows that contain in Excel

This tutorial shows how to Highlight rows that contain in Excel using the example below; Formula =SEARCH(text,cocatenated_columns) Explanation If you want to highlight rows in a table that contain specific text, you use conditional formatting with a formula that returns TRUE when the the text is found. The trick is to concatenate (glue together) the columns you want to search…

Highlight cells that contain one of many in Excel

This tutorial shows how to Highlight cells that contain one of many in Excel using the example below; Formula =SUMPRODUCT(–ISNUMBER(SEARCH(things,A1)))>0 Explanation To highlight cells that contain one of many text strings, you can use a formula based on the functions ISNUMBER and SEARCH, together with the SUMPRODUCT function. In the example shown, the conditional formatting applied to B4:B11 is based on…

Highlight cells that contain in Excel

This tutorial shows how to Highlight cells that contain in Excel  using the example below; Formula =ISNUMBER(SEARCH(substring,A1)) Explanation Note: Excel contains many built-in “presets” for highlighting values with conditional formatting, including a preset to highlight cells that contain specific text. However, if you want more flexibility, you can use your own formula, as explained in this article. If you want to…

Highlight cells that begin with in Excel

This tutorial shows how to Highlight cells that begin with in Excel using the example below; Formula =SEARCH(“substring”,A1)=1 Explanation Note: Excel contains many built-in “presets” for highlighting values with conditional formatting, including a preset to highlight cells that begin with specific text. However, if you want more flexibility, you can use your own formula, as explained in this article. If…

Categorize text with keywords in Excel

This tutorial shows how to Categorize text with keywords in Excel using the example below; Formula {=INDEX(categories,MATCH(TRUE,ISNUMBER (SEARCH(keywords,text)),0))} Explanation To categorize text using keywords with a “contains” match, you can use the SEARCH function, with help from INDEX and MATCH. In the example shown, the formula in C5 is: {=INDEX(categories,MATCH(TRUE, ISNUMBER(SEARCH(keywords,B5)),0))} where “keywords” is the named range E5:E14, and “categories” is the…

IF with wildcards in Excel

This tutorial shows how to calculate IF with wildcards in Excel using the example below; Formula =IF(COUNTIF(A1,”??-????-???”),””,”invalid”) Explanation The IF function doesn’t support wildcards, but you can combine IF with COUNTIF or COUNTIF to get basic wildcard functionality. In the example shown, the formula in C5 is: =IF(COUNTIF(B5,”??-????-???”),””,”invalid”) How this formula works Unlike several other frequently used functions, the IF function does not…

If cell contains in Excel

This tutorial shows how to calculate If cell contains in Excel using the example below; If you want to copy cells that contain certain text, you can use a formula that uses the IF function together with the SEARCH and ISNUMBER functions. Formula =IF(ISNUMBER(SEARCH(“abc”,A1)),A1,””) Explanation Once you find a value you’re looking for you can copy it to another location,…

Match first occurrence does not contain

This tutorial shows how to Match first occurrence does not contain in Excel using the example below; Formula {=MATCH(FALSE,logical_test,0)} Explanation To get the position of the first match that does not contain a specific value, you can use an array formula based on the MATCH, SEARCH, and ISNUMBER functions. In the example shown, the formula in E5 is: {=MATCH(FALSE,data=”red”,0)} where “data” is the named…

Index and match on multiple columns in Excel

This tutorial shows how to calculate Index and match on multiple columns in Excel  using the example below; Formula {=INDEX(range1,MATCH(1,MMULT(–(range2=critera), TRANSPOSE(COLUMN(range2)^0)),0))} Explanation To lookup a value by matching across multiple columns, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and INDEX. In the example shown, the formula in H4 is: {=INDEX(groups,MATCH(1,MMULT(–(names=G4), TRANSPOSE(COLUMN(names)^0)),0))} where “names” is the named range C4:E7, and…

Get last match cell contains in Excel

This tutorial shows how to Get last match cell contains in Excel using the example below; Formula =LOOKUP(2,1/SEARCH(things,A1),things) Explanation To check a cell for one of several things, and return the last match found in the list, you can use a formula based on the LOOKUP and SEARCH functions. In the case of multiple matches found, the formula will return…

Get first match cell contains in Excel

This tutorial shows how to Get first match cell contains in Excel using the example below; Formula {=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0))} Explanation To check a cell for one of several things, and return the first match found in the list, you can use an INDEX / MATCH formula that uses SEARCH or FIND to locate a match. This is an array formula and…

Extract all partial matches in Excel

This tutorial shows how to Extract all partial matches in Excel using the example below; Formula =IF(F5>ct,””,INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5))) Explanation To extract all matches based on a partial match, you can use use an array formula based on the INDEX and AGGREGATE functions, with support from ISNUMBER and SEARCH. In the example shown, the formula in G5 is: =IF(F5>ct,””,INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5))) with the following named…

Count cells that do not contain many strings in Excel

This tutorial shows how to Count cells that do not contain many strings in Excel using the example below; Formula {=SUM(1-(MMULT(–(ISNUMBER(SEARCH(TRANSPOSE (exclude),data))),ROW(exclude)^0)>0))} Explanation To count cells that do not contain many different strings, you can use a rather complex formula based on the MMULT function. In the example shown, the formula in F5 is: {=SUM(1-(MMULT(–(ISNUMBER(SEARCH(TRANSPOSE (exclude),data))),ROW(exclude)^0)>0))} where “data” is the named…

Count rows with multiple OR criteria in Excel

This tutorial shows how to Count rows with multiple OR criteria in Excel using the example below; Formula =SUMPRODUCT(–((criteria1)+(criteria2)>0)) Explanation To count rows using multiple criteria across different columns – with OR logic – you can use the SUMPRODUCT function. In the example shown, the formula in H7 is: =SUMPRODUCT(–((C5:C11=”blue”)+(D5:D11=”dog”)>0)) How this formula works In the example shown, we want to count rows where…

Count rows that contain specific values in Excel

This tutorial shows how to Count rows that contain specific values in Excel using the example below; Formula =SUM(–(MMULT(–(criteria),TRANSPOSE(COLUMN(data)))>0)) Explanation To count rows that contain specific values, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and SUM functions. In the example shown, the formula in G5 is: {=SUM(–(MMULT(–(data=90),TRANSPOSE(COLUMN(data)))>0))} where data is the named range B4:B12. Note: this is an array formula and must be…

Sum if cells contain either x or y in Excel

This tutorial shows how to Sum if cells contain either x or y in Excel using the example below; Formula =SUMPRODUCT(–((ISNUMBER(SEARCH(“cat”,range1)) + ISNUMBER(SEARCH(“rat”,range1)))>0),range2) Explanation To sum if cells contain either one text string or another (i.e. contain “cat” or “rat”) you can use the SUMPRODUCT function. Background When you sum cells with “OR” criteria, you need to be careful not…

Sum if one criteria multiple columns in Excel

This tutorial shows how to Sum if one criteria multiple columns in Excel using the example below; Formula =SUMPRODUCT((criteria_range=”red”)*(sum_range)) Explanation To sum multiple columns conditionally, using one criteria, you can use a formula based on the SUMPRODUCT function. In the example show, the formula in H5 is: =SUMPRODUCT((B5:B10=”red”)*(C5:E10)) How this formula works This first expression in SUMPRODUCT is the criteria,…

Find function vs Search function in Excel

The FIND function and the SEARCH function are very similar to each other. This example shows the difference. 1. To find the position of a substring in a string, use the FIND function. FIND is case-sensitive. 2. To find the position of a substring in a string, use the SEARCH function. SEARCH is case-insensitive. Note: string “excel” found at position 11. Even though it’s…

How to Check If A Cell Contains Specific Text in Excel

To check if a cell contains specific text, you can use the ISNUMBER and the SEARCH function in Excel. The ISNUMBER functions is used to check for a numeric value and the  SEARCH function returns the location of a substring in a string. There’s no CONTAINS function in Excel. 1. To find the position of a substring in a text string, use the SEARCH function.…