Home > Formulas > Text > SEARCH 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 Microsoft 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:
In the screenshot below, 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))) |
Ex 4: Reverse Names - Long FormulaIn this example, there is a list of names on an Excel spreadsheet. Currently, the names are in this format - Last Name, First Name. To reverse the names (First Name Last Name), you can use the following Excel formula. Along with the SEARCH function, it uses the RIGHT function, LEN function, and LEFT function.
Video: Reverse Names - Long FormulaThis video shows how to build the formula, and there are written steps, and the full video transcript, on the Split Names with a Formula page.
|
Get the Sample Files
Excel TopicsData Val | Pivot | Formulas | Macros | Format | Charts | Filters More Functions Tutorials |
Last updated: May 22, 2023 4:26 PM