Excel SEARCH FunctionThese Excel SEARCH function examples show how to look for a text string, within another text string, and return its position, if found. Video, written steps and free workbook Author: Debra Dalgleish |
Video: SEARCH FunctionThe Excel SEARCH function looks for a text string, within another text string, and returns its position, if found. Watch this short video to see 3 examples of how to use SEARCH. There are written steps below the video. Uses for SEARCHThe SEARCH function looks for a text string, within another text string, and it can:
|
Ex 3: Specify Start Number for SEARCHBy typing two minus signs (double unary) in front of the ISNUMBER function, the formula returns a result of 1 (one) or 0 (zero) instead of TRUE/FALSE. With those numeric results, a SUM function in cell E2 can total the number of records where the text string was found. In this example, City and Occupation are shown in column B. We want to find all occupations with the text string entered in cell E1. The below formula is in cell C2, with two minus signs before the ISNUMBER function:
The formula found the string that contain "bank", but one of those is in a City name, not the occupation: There is a pipe character after each city name, so we can add a SEARCH for that. Its position can be used as the start_number argument in the main SEARCH, so the cities will be ignored when searching. Now, with the revised formula, only the rows with "bank" in the occupation are counted. =–ISNUMBER(SEARCH($E$1,B2,SEARCH("|",B2))) |
Get the Sample FilesTo see the formulas used in these examples, get the SEARCH function sample workbook. The file is zipped, and is in Excel xlsx format, with no macros. Excel TopicsData Val | Pivot | Formulas | Macros | Format | Charts | Filters More Functions Tutorials |
Last updated: January 16, 2023 7:42 PM