FIND Function and SEARCH Function in Excel are invaluable tools for locating specific text within your spreadsheets. Whether you’re searching for a single word or a substring, these functions offer precise ways to identify the position of text within a cell. Say goodbye to manual scanning and hello to efficient text searches with FIND Function and SEARCH Function in Excel. Whether you’re analyzing large datasets, cleaning up data, or extracting specific information, these functions provide the flexibility and accuracy you need. Embrace the power of FIND and SEARCH to streamline your text-based operations and enhance your productivity. With just a few simple formulas, you can quickly locate and extract the information you need, making your Excel experience smoother and more efficient.
This Content Covers:
- Definition of FIND Function and Search Function.
- Syntax and Arguments of FIND and SEARCH Functions
- Excel FIND vs. Excel SEARCH
3.1 Case-sensitive FIND vs. Case-Insensitive SEARCH
3.2 Working with Wildcard Characters
1. Definition of FIND Function and Search Function.
FIND Function: The FIND function in Excel is a built-in function that is used to extract the numerical location of one text string inside another. It is categorized as a String/Text Function. The FIND function returns a #VALUE error if the text cannot be located. FIND function is a case-sensitive function.
SEARCH Function: Excel’s SEARCH function is quite similar to FIND since it also gives the position of a substring within a text string. Though it’s similar to FIND function, the major difference between these two functions is that SEARthe CH function is case intensive, which means it can work with wildcard characters.
2. Syntax and Arguments of FIND and SEARCH Functions
Syntax:
FIND- FIND( substring, string, [start_position] )
SEARCH- SEARCH(substring, string, [start_position]
Arguments: Both of these functions have the same arguments.
Substring- The substring to find.
String- The text to look up in.
Start position- [optional] specifies the character from which the search should start.
3. Excel FIND vs. Excel SEARCH
3.1 Case-sensitive FIND vs. Case-Insensitive SEARCH
Step 1: Let’s look at the FIND function first, in the picture below we have a text string “Bamboo”. Now I want to know the position of “b” inside the text string so I used FIND function to locate it’s position. The function here returned 4 as we can clearly see the letter “b” is at the 4th position in this text.
Step 2: Now we will use the SEARCH function in cell B3 to locate the position of “b”. But the function here returned 1 because as the title of this particular section says, FIND function is case sensitive so it only looked for the lower case “b” inside the text string and ignored the upper case “B”. SEARCH function being case intensive didn’t care about the case of the substring and located the position of the first “b”.
Step 3: If we use an upper case “B” as the substring then FIND function will also return 1 as you can see in the below given picture.
Step 4: Both the function returns #VALUE error if they are unable to locate the text.
3.2 Working with Wildcard Characters
In contrast to FIND, the Excel SEARCH function supports wildcard characters in the find text input.
Step 1: In the picture below, we have used a wildcard character inside the FIND formula. Though there is an “x” in the text string and a “2019”, the formula returned an error text.
Step 2: The text string referred to in the within text parameter contains both “x” and “2019”. So, the formula returns the location of the first character (“x”) in the substring, as seen in the picture below, regardless of how many characters are in between.
4. How to use FIND Function and SEARCH Function in Excel? (With Examples)
Now we will see how we can utilize FIND and SEARCH function through some examples.
Example 1: In this example we will learn how we can use FIND or SEARCH function with LEFT, RIGHT and LEN function to locate a text string preceding or following a given substring using this below given data.
Step 1: Select cell B2 and insert the below given formula. Here we have used the FIND function with LEFT function. The LEFT function in Excel returns the requested number of a string’s left-most characters. Additionally, you locate a space (” “) using the FIND function to tell the LEFT function how many characters to extract. In order to avoid having the space included in the returned value, you then deduct 1 from the space’s location.
=LEFT(A2,FIND(” “,A2)-1)
Step 2: Now use the same formula in B3 but use SEARCH instead of FIND function this time.
Step 3: To extract the last name from the text string we have to use FIND or SEARCH function with RIGHT and LEN function. The LEN function here is needed to get the total number of characters inside the text string from which you subtract the position of the space.
Step 4: Now use the same formula with SEARCH function in C3.
Example 2: In this situation column A has a lengthy text string and you want to locate and extract only the content wrapped in (parentheses).
Step 1: To do this you would require the Excel FIND or SEARCH function to decide where to start and how many characters to extract, as well as the MID function to retrieve the desired number of characters from a string.
Step 2: Now let’s use the same formula in B3 with SEARCH function.
Example 3: Suppose you want to locate the third dash (-) in a string of text from the below picture.
Step 1: Insert the following formula in cell B2 and hit Enter key.
Step 2: Now let’s locate the position of the second dash (-) from the text string of cell A3 using SEARCH function. This formula’s first two parameters seek out a dash (“-“) in cell A2. Another FIND function is included in the third input (start num), instructing Excel to begin looking after the character that follows the initial dash (SEARCH(“-“,A2)+1).
Application of FIND Function and SEARCH Function in Excel
- Locating Text: Use the FIND and SEARCH functions in Excel to locate specific text or substrings within a cell or range, facilitating data retrieval and analysis.
- Data Cleaning: Identify and extract specific text patterns or characters using FIND and SEARCH, aiding in data cleaning and standardization tasks.
- Conditional Formatting: Apply conditional formatting based on the presence or absence of certain text strings found using FIND and SEARCH, enhancing data visualization and analysis.
- Substring Extraction: Extract substrings from larger text strings based on specific criteria identified with FIND and SEARCH, enabling targeted data extraction for further analysis.
- Error Checking: Utilize FIND and SEARCH to identify potential errors or inconsistencies in text data, allowing for proactive error checking and data validation.
- Formula Building: Incorporate the results of FIND and SEARCH functions into more complex formulas to perform advanced text manipulation or analysis tasks in Excel.
For ready-to-use Dashboard Templates:
