How to use Excel FIND Function (Example + Video)

Excel FIND Function

When to Use the Excel FIND Function in Excel

The FIND function comes to the rescue when you need to determine the position of a specific character or substring within a text string. Whether you are extracting information, replacing characters, or performing complex data transformations, FIND offers a powerful way to navigate through your data.

What Excel FIND Function Returns

Unlike some functions that return values, the FIND function returns the position of the first occurrence of a specified substring within a text string. This position is counted from the first character of the text string, with spaces included in the count.

Syntax of Excel FIND Function

The syntax of the Excel FIND function is as follows:

=FIND(find_text, within_text, [start_num])

Input Arguments

Here are the input arguments for the Excel FIND function:

👉 find_text: The substring you’re searching for within the within_text. This can be a direct entry enclosed in quotation marks, a cell reference, or a formula resulting in a text string.

👉 within_text: The text string that you want to search within. Similar to find_text, this can be a direct entry, a cell reference, or a formula.

👉 start_num (optional): The character position within within_text where the search should begin. If omitted, the search starts from the first character.

Extra Notes

👉 If find_text is not found within within_text, the function returns an error (#VALUE!).

👉 If start_num exceeds the length of within_text, the function also returns an error (#VALUE!).

Examples of the Excel FIND Function

Let’s explore some examples showcasing the utility of the Excel FIND function:

Example 1: Basic Usage

Suppose cell A1 contains the text “Excel Functions,” and you want to find the position of the word “Functions.” Utilize the formula:

Example 2: Extracting Substring Using FIND

Suppose you have URLs in column A, and you want to extract the domain from each URL. You can use FIND in combination with LEFT and MID functions:

Here’s the breakdown of the formula:

(1) FIND(“://”, A1)

• This part of the formula locates the position of the string “://” within the URL.
• In the given URL “https://www.example.com/page”, the result of this portion is 6.
• Adding 3 to this result (+3) ensures that we start searching after “://” and the following 3 characters.

(2) FIND(“/”, A1, FIND(“://”, A1)+3)

• This part locates the position of the first “/” character after the protocol and subdomain.
• Using the result of the previous portion (position after “://”), which is 9, as the starting position.
• In the given URL, the result of this portion is 21, as it’s the position of the first “/” after “://”.

(3) FIND(“/”, A1, FIND(“://”, A1)+3) – FIND(“://”, A1) – 3

• This part calculates the length of the domain portion of the URL.
• It subtracts the position after “://” (9) from the position of the first “/” after “://” (21), which equals 12.
• Then, subtracting 3 from this length (- 3) ensures that we don’t include the characters “://”.

(4) MID(A1, FIND(“://”, A1)+3, FIND(“/”, A1, FIND(“://”, A1)+3) – FIND(“://”, A1) – 3):

• This part combines the MID function with the calculated positions and length to extract the domain.
• MID(A1, FIND(“://”, A1)+3, FIND(“/”, A1, FIND(“://”, A1)+3) – FIND(“://”, A1) – 3) extracts a substring from the URL.
• The starting position is the result of the first portion (9) and the length is the result of the third portion (12).
• So, it extracts the substring starting at position 9 and of length 12.

Difference Between FIND Function & SEARCH Function

Both the FIND and SEARCH functions in Excel are used to locate the position of a specific substring within a text string. However, there is a crucial difference between the two functions: case-sensitivity.

• Use FIND when you want a case-sensitive search. For example, if you’re looking for the word “apple”, FIND will only find “apple” and not “Apple” or “APPLE”.
• Use SEARCH when you want a case-insensitive search. For instance, if you’re searching for the word “apple”, SEARCH will find “apple”, “Apple” and “APPLE”.

Excel FIND Function – Video Tutorial

Practice Workbook

Conclusion

The Excel FIND function empowers you to navigate and manipulate text strings with precision. Its ability to locate specific characters within a text string is invaluable for data processing, text analysis, and more. As with any Excel function, practice is key to mastery. Experiment, explore, and elevate your Excel skills by incorporating the Excel FIND function into your toolkit.

Remember, embracing Excel functions like FIND takes practice and experimentation. As you become familiar with its nuances, you will unlock new possibilities for data manipulation and analysis. Start using the FIND function today and propel your Excel proficiency to greater heights!

Frequently Asked Questions (FAQs)

Q1: Can the FIND function locate the position of the last occurrence of a substring?

A1: No, the FIND function identifies the position of the first occurrence only. For the position of the last occurrence, consider using the combination of FIND and RIGHT functions.

Q2: Is the FIND function case-sensitive?

A2: Yes, by default, the FIND function is case-sensitive. To perform a case-insensitive search, consider using the SEARCH function.

Q3: What happens if the searched substring is not found?

A3: In such cases, the FIND function returns an error (#VALUE!).

Q4: Can variables be used as arguments in the FIND function?

A4: Yes, you can use variables, cell references, or direct entries as arguments.

Q5: Can FIND be employed to search for multiple substrings at once?

A5: No, FIND searches for a single substring at a time. To search for multiple substrings, consider nesting FIND within other functions.

Q6: Does FIND support wildcard characters for searching?

A6: No, FIND doesn’t support wildcard characters. For wildcard searches, explore the capabilities of other Excel text functions.

Other Related Excel Functions

LEN Function

UPPER Function

LOWER Function

Join me on Instagram and YouTube for your daily dose of valuable tips and tricks! Catch insightful videos that will enhance your knowledge and skills. Don’t miss out – follow me now!