Excel FIND function is used to search a substring within a text string and return its starting position within the text. The FIND function is a powerful tool for data analysis and manipulation, especially when working with large data sets. This function can be used to extract information from strings, validate data entry, and perform complex calculations.
FIND Function Summary
The FIND Function Explained: How to Locate Specific Data in Excel. The FIND Function belongs to the family of Excel TEXT functions and is used to find the position of a character or sub-string within a text string. By understanding how this function works, you can quickly locate and extract the data you need for financial analysis and other applications.
FIND Function Purpose
The Excel FIND function is used to locate the position of a substring within a text string. It is commonly used for the following purposes:
- Finding the starting position of a word or character within a text string.
- Extracting a part of a text string based on its position.
- Validating data entry by ensuring that a specific value is present in a cell.
FIND Function Arguments
The Excel FIND function has two arguments:
- Find_text (required): The substring you want to find within the text string.
- Within_text (required): The text string in which you want to search for the substring.
FIND Function Return value
The Excel FIND function returns the position of the first occurrence of the substring within the text string. If the substring is not found, the function returns the #VALUE! error.
FIND Function Syntax
The syntax for the Excel FIND function is:
=FIND(find_text, within_text, [start_num])Excel FIND Function Examples
In this section, we will explore several practical examples of using the FIND function in various scenarios. From basic usage to more complex cases involving the IF and LEFT functions, you’ll learn how to make the most of the FIND function in your Excel spreadsheets.
Finding the Position of a Sub-String in a Text String using the FIND Function
Say you want to search for the sub-string “apple” within the text string “I like to eat apples” and returns the position of the first character of the sub-string.
To do this you can use the following formula:
=FIND("apple", A1)
This formula will return the value 15, which is the starting position of the word “apple” within the text string “I like to eat apples”.
Extracting Substrings with LEFT and FIND Functions
Say you want to extract a substring from a larger string. For instance say we have “I like to eat apples” in cell A1. We want it to read ‘I like to eat”.
To do this you can use the following formula:
=LEFT(A1, FIND("apples",A1)-1)
This formula demonstrates how to extract a substring from a larger string using the LEFT and FIND functions. The FIND function locates the position of the word “apples” in the string “I like to eat apples”, and the LEFT function then extracts the characters to the left of that position, giving us the string “I like to eat”.
Using FIND and IF Functions to Check for Text String Presence in Cells
In this instance we want to use the FIND function to locate the position of a specified character or sub-string within a supplied text string.
In this instance we want to use the FIND function to search for the word “dog” within the contents of cell A1. If the search term is not found, the function returns the message “Not Found”. If the term is found, the IF function returns the message “Found”.
This is a useful formula for checking if a specific word or phrase is present in a cell, which can be helpful for data analysis or data cleaning tasks.
To do this you can use the following formula:
=IF(ISERROR(FIND("dog", A1)), "Not Found", "Found")
The formula has checked if the text string in cell A1 contains the word “dog”. As it does in our example, the formula will return “Found”. If the cell A1 contained “Dog” the formula would return “Not Found”.
Excel FIND Function Notes
- The Excel FIND function is case-sensitive. This means that it will only find exact matches of the substring in the text string.
- The Excel FIND function is not suitable for searching for multiple occurrences of a substring within a text string. For this purpose, you can use the Excel SEARCH or FINDALL function.