FIND Function in Excel

The FIND function is one of the (TEXT) functions of Excel. It returns the starting position of one

text string within another text string. FIND is case-sensitive.

We can find this function in TEXT of insert function Tab.

How to use FIND function in excel

1. Click on empty cell (like F5 )

2. Click on fx on the below of font word (or press shift+F3)

3. In insert function tab you will see all functions

4. Select TEXT category

5. Select FIND function

6. Then select ok

7. In function arguments Tab you will see FIND function

8. Find text is the text you want to find. Use double quotes (empty text) to match the first

character in Within text; wildcard characters not allowed

9. Within text is the text containing the text you want to find

10. Start num specifies the character at which to start the search. The first character in Within

text is character number 1. If omitted, Start_num = 1

11. You will see the results in formula result section

Examples of FIND function in Excel

Here are ten examples of how to use the FIND function in Excel:

1. To find the position of a specific character within a text string, use the formula =FIND("a", "apple") which returns the value 1.
2. To find the position of the last occurrence of a specific character within a text string, use the formula =FIND("#", "apple#banana#cherry#") which returns the value 19.
3. To find the position of the first occurrence of a specific text string within another text string, use the formula =FIND("apple", "I like apples and bananas") which returns the value 8.
4. To find the position of the last occurrence of a specific text string within another text string, use the formula =FIND("apple", "I like apples and bananas, especially apple pie") which returns the value 28.
5. To find the position of a specific text string within a range of cells, use the formula =FIND("apple", A1:A10) which returns an array of values indicating the positions of “apple” within each cell in the range.
6. To find the position of a specific text string within a cell, ignoring case sensitivity, use the formula =FIND("apple",B2,1) where the third argument “1” specifies a case-insensitive search.
7. To find the position of a specific text string within a cell, starting from a specified position, use the formula =FIND("apple",B2,5) where the third argument “5” specifies the starting position for the search.
8. To check if a specific text string is present within another text string, use the formula =IF(ISNUMBER(FIND("apple",B2)),"Yes","No") which returns the value “Yes” if “apple” is found in cell B2, or “No” otherwise.
9. To extract a substring of text from a larger text string starting at a specified position and ending at another specified position, use the formula =MID("I like apples", 3, 4) which returns the value “like”.
10. To extract a substring of text from a larger text string starting at a specific position and continuing for a specified number of characters, use the formula =RIGHT(A1,5) which returns the last five characters of the text string in cell A1.

How do I use the FIND function?

Certainly!

The FIND function in Excel is used to find the position of a specific character or text string within a larger text string. Its syntax is as follows:

=FIND(find_text, within_text, [start_num])

Where:

• find_text: The character or text string that you want to find.
• within_text: The larger text string in which you want to search for the find_text.
• start_num (optional): The starting position of the search. If not specified, the default value is 1.

Here are some examples to demonstrate how to use the FIND function:

Example 1: Suppose you have the text string “I like apples and bananas” in cell A1 and you want to find the position of the word “apples”. You can use the FIND function as follows:

=FIND("apples",A1)

This formula will return the result 8, indicating that the word “apples” starts at the 8th position in the text string.

Example 2: Suppose you have a list of product codes in column A and you want to extract the first three characters from each code. Assuming that each code is 6 characters long, you can use the FIND function in combination with the LEFT function as follows:

=LEFT(A1, FIND(" ", A1)-1)

This formula will return the first three characters from the product code in cell A1.

Example 3: Suppose you have a list of email addresses in column A and you want to extract the domain name from each address. Assuming that all email addresses have the format “username@domain.com“, you can use the FIND function in combination with the RIGHT function as follows:

=RIGHT(A1,FIND("@",A1)+LEN("@")-1)

This formula will return the domain name from the email address in cell A1.

These are just a few examples of how to use the FIND function in Excel. By using this function, you can easily locate specific characters or text strings within larger text strings and use that information to manipulate or extract data as needed.

Can the FIND function be used to find multiple occurrences of a character or text string?

No, the FIND function in Excel can only return the position of the first occurrence of a specific character or text string within a larger text string. If you want to find all occurrences of a character or text string, you will need to use a different function.

One way to find multiple occurrences of a character or text string is to use the SEARCH function instead of the FIND function. The syntax of the SEARCH function is similar to that of the FIND function:

=SEARCH(find_text, within_text, [start_num])

Where:

• find_text: The character or text string that you want to find.
• within_text: The larger text string in which you want to search for the find_text.
• start_num (optional): The starting position of the search. If not specified, the default value is 1.

The difference between the FIND and SEARCH functions is that the SEARCH function is case-insensitive, meaning that it can find both uppercase and lowercase characters. Here are some examples to demonstrate how to use the SEARCH function:

Example 1: Suppose you have the text string “bananas are yellow and bananas are tasty” in cell A1 and you want to find the positions of all occurrences of the word “bananas”. You can use the SEARCH function as follows:

=SEARCH("bananas",A1)

This formula will return the results 1 and 20, indicating that the word “bananas” appears at the 1st and 20th positions in the text string.

Example 2: Suppose you have a list of product descriptions in column A and you want to count the number of times the word “red” appears in each description. You can use the SEARCH function in combination with the LEN and SUBSTITUTE functions as follows:

=LEN(SUBSTITUTE(A1,"red",""))/(LEN(A1)-LEN(SUBSTITUTE(A1,"red","")))

This formula will return the number of times the word “red” appears in the product description in cell A1.

By using the SEARCH function, you can find all occurrences of a character or text string within a larger text string and use that information to manipulate or extract data as needed.

How can I ignore case sensitivity when using the FIND function?

The FIND function in Excel is case-sensitive by default, meaning that it will only find the first occurrence of a character or text string that matches the case of the search term. However, you can use the SEARCH function instead of the FIND function to ignore case sensitivity in your searches.

The syntax of the SEARCH function is similar to that of the FIND function:

=SEARCH(find_text, within_text, [start_num])

Where:

• find_text: The character or text string that you want to find.
• within_text: The larger text string in which you want to search for the find_text.
• start_num (optional): The starting position of the search. If not specified, the default value is 1.

The difference between the FIND and SEARCH functions is that the SEARCH function is case-insensitive, meaning that it can find both uppercase and lowercase characters. Here are some examples to demonstrate how to use the SEARCH function to ignore case sensitivity:

Example 1: Suppose you have the text string “I like apples” in cell A1 and you want to find the position of the word “APPLES” regardless of whether it is uppercase or lowercase. You can use the SEARCH function as follows:

=SEARCH("apples",LOWER(A1))

This formula converts the original text string to lowercase using the LOWER function and then searches for the lowercase version of the search term “apples”. This will return the result 8, indicating that the word “apples” starts at the 8th position in the text string.

Example 2: Suppose you have a list of product descriptions in column A and you want to count the number of times the word “red” appears in each description regardless of case sensitivity. You can use the SEARCH function in combination with the LEN and SUBSTITUTE functions as follows:

=LEN(SUBSTITUTE(LOWER(A1),"red",""))/(LEN(LOWER(A1))-LEN(SUBSTITUTE(LOWER(A1),"red","")))

This formula converts the original text string to lowercase using the LOWER function and then searches for the lowercase version of the search term “red”. This will return the number of times the word “red” appears in the product description in cell A1 regardless of case sensitivity.

By using the SEARCH function in combination with other functions, you can ignore case sensitivity in your searches and manipulate or extract data as needed.

Can I use wildcards with the FIND function?

No, the FIND function in Excel does not support the use of wildcards. Wildcards are characters that can represent one or more other characters and are commonly used in pattern-matching searches.

However, you can use other functions in Excel that support wildcards to find specific patterns of characters within a text string. Here are some examples:

Example 1: Suppose you have a list of email addresses in column A and you want to extract all email addresses that end with “.com”. You can use the FILTER function in combination with the SEARCH function as follows:

=FILTER(A:A,ISNUMBER(SEARCH(".com",A:A)))

This formula returns all email addresses that contain the “.com” string anywhere in the text.

Example 2: Suppose you have a list of product descriptions in column A and you want to extract all descriptions that contain the word “red” followed by any two characters. You can use the FILTER function in combination with the SEARCH function and a wildcard character as follows:

=FILTER(A:A,ISNUMBER(SEARCH("red??",A:A)))

This formula returns all product descriptions that contain the string “red” followed by any two characters.

By using functions such as FILTER, SEARCH, and ISNUMBER in combination, you can search for specific patterns of characters within text strings and extract data as needed. While the FIND function does not support the use of wildcards, these other functions provide similar functionality and flexibility when searching through large amounts of data.

How do I find the position of the last occurrence of a character or text string within a larger text string?

To find the position of the last occurrence of a character or text string within a larger text string in Excel, you can use a combination of the LEN, FIND, and REVERSE functions.

The basic idea is to reverse the order of the text string using the REVERSE function, then search for the first occurrence of the character or text string using the FIND function, and finally subtract the position from the total length of the text string using the LEN function to get the position of the last occurrence.

Here’s an example to illustrate this:

Suppose you have the text string “I like bananas and apples and bananas” in cell A1 and you want to find the position of the last occurrence of the word “bananas”. You can use the following formula:

=LEN(A1)-FIND(REVERSE("sananab"),REVERSE(A1))+1

Let’s break down this formula step by step:

1. The REVERSE function is used to reverse the order of the text string “bananas”, resulting in the string “sananab”. This is necessary because we want to search for the last occurrence of the word “bananas” starting from the end of the text string.
2. The REVERSE function is applied again to the original text string A1 to reverse its order.
3. The FIND function is used to find the first occurrence of the reversed string “sananab” within the reversed text string A1.
4. The result of step 3 is subtracted from the total length of the text string A1 using the LEN function, and the result is then incremented by 1 to get the position of the last occurrence of the word “bananas”.

In this example, the formula returns the result 28, indicating that the last occurrence of the word “bananas” starts at the 28th position in the text string.

By using a combination of the LEN, FIND, and REVERSE functions in Excel, you can easily find the position of the last occurrence of a character or text string within a larger text string.

Can I use the FIND function to search for a text string within a range of cells?

The FIND function in Excel can be used to search for a text string within a single cell. However, if you want to search for a text string within a range of cells, you will need to use a combination of other functions such as SUMPRODUCT, OFFSET, INDEX or MATCH.

Here are some examples to demonstrate how to use the FIND function to search for a text string within a range of cells:

Example 1: Suppose you have a list of product codes in column A and you want to find all the codes that contain the substring “ABC”. You can use the following formula:

=FILTER(A:A,SUMPRODUCT(--(ISNUMBER(FIND("ABC",A:A)))))

This formula uses the FIND function to search for the substring “ABC” within each cell in column A. The ISNUMBER function returns TRUE if the substring is found, and FALSE otherwise. The double negative (–) coerces the TRUE or FALSE values into 1s and 0s, respectively. The SUMPRODUCT function then adds up these 1s and 0s to return the total number of times the substring “ABC” is found. Finally, the FILTER function is used to extract only the cells in column A that contain the substring “ABC”.

Example 2: Suppose you have a table of sales data with product names in column A and sales figures in column B, and you want to find the total sales for all products that contain the word “apple”. You can use the following formula:

=SUM(OFFSET(B2,MATCH("*apple*",A2:A10,0)-1,0,COUNTIF(A2:A10,"*apple*"),1))

This formula uses the MATCH function to find the first occurrence of the text string “apple” within the range A2:A10. The * character is a wildcard that matches any number of characters before or after the word “apple”. The OFFSET function is then used to move down from the starting cell B2 by the number of rows returned by the MATCH function minus 1, and it returns a range that covers the same number of rows as the COUNTIF function, which counts the number of cells in column A that contain the word “apple”. Finally, the SUM function adds up the sales figures in this range.

By using a combination of functions such as SUMPRODUCT, OFFSET, INDEX, or MATCH in Excel, you can search for a text string within a range of cells and extract data as needed.

Can I use the FIND function to search for a text string in a specific column or row?

The FIND function in Excel only searches within a single cell or range of cells, and cannot be used to search within a specific column or row. However, you can use other functions such as INDEX, MATCH, and IFERROR to locate a text string in a specific column or row.

Here are some examples to demonstrate how to use these functions to search for a text string in a specific column or row:

Example 1: Suppose you have a table of sales data with product names in column A and sales figures in columns B through F, and you want to find the total sales for a specific product name. You can use the following formula:

=SUM(INDEX(B:F,0,MATCH("Product Name",A: A,0)))

This formula uses the MATCH function to find the column number that contains the heading “Product Name” in row 1 of the table. The INDEX function is then used to return a reference to the entire column range B:F, and the column number returned by the MATCH function is used as the column argument. Finally, the SUM function adds up the sales figures in this column range.

Example 2: Suppose you have a list of employee names in column A and their corresponding department names in column B, and you want to determine whether a specific employee name appears in the list. You can use the following formula:

=IFERROR(MATCH("John Doe",A:A,0),"Not Found")

This formula uses the MATCH function to find the row number that contains the value “John Doe” within the range A:A. If the value is found, the MATCH function returns the row number; otherwise, it returns an error value. The IFERROR function is then used to display the message “Not Found” in case of an error.

By using functions such as INDEX, MATCH, and IFERROR in combination, you can search for a text string in a specific column or row and extract data as needed. While the FIND function cannot be used to search within a specific column or row, these other functions provide similar functionality and flexibility for working with large amounts of data.

How do I handle errors when using the FIND function?

When using the FIND function in Excel, it is possible to encounter errors such as #VALUE!, #REF!, and #NAME?. These errors can occur for a variety of reasons, such as an invalid argument or a search term that is not found within the text string. Here are some ways to handle errors when using the FIND function:

1. Use the IFERROR Function: The IFERROR function allows you to handle errors gracefully by returning a specified value when an error occurs. For example, suppose you have the formula =FIND("apple", A1) to find the position of “apple” in cell A1. If “apple” is not found in A1, this formula will return a #VALUE! error. You can use the IFERROR function to return a custom message instead, like this:

=IFERROR(FIND("apple",A1),"Not Found")

This formula returns the message “Not Found” if the search term “apple” is not found within the text string.

2. Check for Errors with the ISERROR Function: The ISERROR function allows you to check for errors in a formula and return TRUE if an error is present, or FALSE otherwise. For example, if you have the formula =FIND("apple", A1), you can use the ISERROR function to check for errors like this:

=ISERROR(FIND("apple",A1))

This formula returns TRUE if the search term “apple” is not found within the text string or if there is another error present in the formula.

3. Use Conditional Formatting to Highlight Errors: Conditional formatting allows you to highlight cells that contain errors in a specific color or format. For example, you can highlight all cells that contain a #VALUE! error by selecting the range of cells you want to format, clicking on “Conditional Formatting” in the Home tab of the ribbon, selecting “New Rule…”, choosing “Format only cells that contain”, selecting “Errors” in the drop-down menu, and choosing the color or format you want to use.

By handling errors appropriately when using the FIND function in Excel, you can ensure that your formulas work correctly and provide reliable results.

Can I use the FIND function to extract a substring from a larger text string?

Yes, you can use the FIND function in Excel to extract a substring from a larger text string. The FIND function returns the position of a character or text string within a larger text string, which can be used as the starting point for extracting a substring using the LEFT, MID, or RIGHT functions.

Here are some examples to demonstrate how to use the FIND function to extract a substring from a larger text string:

Example 1: Suppose you have the text string “John Doe (123-45-6789)” in cell A1 and you want to extract only the Social Security number “123-45-6789”. You can use the following formula:

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)

This formula uses the FIND function to locate the position of the opening parenthesis “(” and the closing parenthesis “)” within the text string A1. The MID function is then used to extract the characters between these two positions using the starting position returned by the first FIND function and the length of the substring calculated as the difference between the positions returned by the second and first FIND functions.

Example 2: Suppose you have a list of email addresses in column A and you want to extract only the domain name (e.g., “gmail.com“) from each address. You can use the following formula:

=RIGHT(A1,LEN(A1)-FIND("@",A1))

This formula uses the FIND function to locate the position of the “@” symbol within the text string A1. The LEN function is then used to return the total number of characters in the text string A1, and the difference between this number and the position of the “@” symbol is used as the length argument for the RIGHT function, which extracts the characters to the right of the “@” symbol.

By using the FIND function in combination with other functions such as LEFT, MID, and RIGHT, you can extract specific substrings from larger text strings in Excel.

Related posts:

Excel RIGHT Function Excel LOWER Function REPT Function in Excel Excel FIXED Function