What Is FIND Function In Google Sheets?
The FIND function in Google Sheets identifies the text string stored in any cell. It returns the position of the searched string as output and only takes the text format as searched reference.
The Google Sheets FIND function is case-sensitive. The function will check the position of the string, whether the search is for an uppercase or lowercase alphabet. The space in-between the words, special characters and symbols are considered as characters. For example, the table below has a string for which we must find a singles character’s position using the FIND function.
Select cell C2, enter the formula =find(B2,A2) and press “Enter”.
The output is shown above. The character “l” is in the 4th position, within the string “Apple”.
Table of Contents
Key Takeaways
- The FIND function in Google Sheets returns the location or position of the string, from a sentence or group of words w.r.t to the arguments search_for, text_to_search and [starting_at].
- The function counts the position of the special characters, symbols and space whether at the start, end or in-between the words.
- The function returns the error, if the “search_for” value does not exist in the “text_to_search” string, if the “[starting_at]” contains more characters than the “text_to_search” strings or if the [starting_at] is 0 [zero] or a negative number.
- We can use the FIND function with MID(), LEN(), SUBSTITUTE(), TRIM(), RIGHT(), LEFT(), etc. and many more functions.
Find() Google Sheets Formula
The syntax of the FIND Google Sheets Formula is,
The arguments of the FIND Google Sheets Formula are,
- search_for 🠖 It searches a substring or an alphabet and is case-sensitive.
- text_to_search 🠖 It is a string, a group of words or a sentence, within which we “search_for”.
- [starting_at] 🠖 The position, of a string or a group of words or a sentence, from where the search begins. It is an optional argument. [If no number is given, then by default the FIND function will calculate from the first position].
How To Use FIND Function in Google Sheets?
We can use the FIND function In Google Sheets in 2 ways, namely,
- Access from the Google Sheets ribbon.
- Enter in the worksheet manually.
Method #1 – Access from the Google Sheets ribbon 🡢
Step 1: Choose an empty cell for the output 🠖 select the “Insert” tab 🠖 click the “Function” option right arrow 🠖 click the “Text” option right arrow 🠖 select the “FIND” function, as shown below.
Step 2: The “FIND” formula appears, as shown below. Enter the argument as the values or cell reference.
Method #2 – Enter in the worksheet manually 🡢
Step 1: Select an empty cell for the output.
Step 2: Type =FIND( in the cell. [Alternatively, type =F or =FI and double-click the FIND function from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references and close the brackets.
Step 4: Press Enter to view the outcome.
Examples
We will consider some examples to understand Google Sheets Find function.
Example #1 – Removing Leading Spaces
We have a name with some leading spaces, as shown in the below table. We will Removing Leading Spaces using the FIND function.
Select cell B2, enter the formula =TRIM(MID(A2,FIND(“/”,SUBSTITUTE(A2,” “,”/”)),LEN(A2))) and press “Enter”, as shown below.
The output is shown above.
Example #2 – Extracting Domain Names
We have a column of email addresses in the below dataset. We will extract the domain names using the FIND function to locate the position of the “@” symbol and extract the characters after it.
The steps to extract Domain name using the FIND function are,
Step 1: Select cell D2 and enter the formula =RIGHT(B2,LEN(B2)-FIND(“@”,B2)), as shown below.
Step 2: Press “Enter” and drag the formula from cell D2 to D3 using the fill handle to get the output shown below.
Example #3 – Finding a Specific Text in a String
The table below has a proverb and we must find the text using the FIND function.
Select cell C2, enter the formula =find(B2,A2) and press “Enter”.
The output is shown above. The text work is in the 1st position.
Example #4 – Using FIND Function in Combination with MID Function
We have a phrase as shown in the below data. We will extract certain characters using the FIND Function in Combination with MID Function.
The steps to use the Find function with MID() are as follows,
Step 1: Select cell B2 and enter the formula =MID(A2,FIND(“*”,A2),FIND(” “,MID(A2,FIND(“*”,A2),LEN(A2)))), as shown below.
Step 2: Press “Enter”, to get the output shown below.
The extracted characters from the cell is “*someone”. Let us understand the formula as follows:
- MID() 🠖 The MID function checks the string from the middle.
- LEN() 🠖 The LEN function counts the length of the string. The “search_for” value is “*” and the “text_to_search” value is “A2”.
- Also note, we have a blank space before the 2nd MID function. [ “ ” , MID(A2,FIND(“*”,A2),FIND(” “,MID]
Example #5 – Finding the Second Occurrence of a Specific Text
Let us consider example 3 once again. The table below has a proverb and we must find the second occurrence of the text using the FIND function.
Select cell C2, enter the formula =find(B2,A2) and press “Enter”. We get the output shown below of the position of the first occurance of the word “work”.
Now, since the FIND function is case sensitive, we can change one of the words in the proverb to the opposite case, as shown in the cell A5.
Finally, select cell B5 and enter the formula =find(B5,A5). We get the following output, i.e., the position of the second occurrence of the word “work”, as 16, because the function ignored the word “Work”.
Example #6
The table below has a strings and we will find a words position using the FIND function.
The steps to find the position of the word using the FIND function area as follows:
Step 1: Select cell C2 and enter the formula =find(B2,A2), as shown below.
Step 2: Press “Enter” to get the output. It is as shown below.
Step 3: Drag the formula from cell C2 to C7 using the fill handle.
Every result cell has returned the position of the selected string, except for cell C7 as the “w” in “web” is in lowercase and the letter to search is in uppercase. We can either change the case of cell B7 or leave it as its is. Also, note that we were able to drag the formula to the rest of the cells only because the search character was same for all the cells and we have given the cell reference as the argument.
Important Things To Note
- The FIND function always looks for the first occurrence of the “search_for”.
- The arguments can be supplied as cell references or direct text strings. To supply these arguments as direct text strings, they must be enclosed within double quotation marks.
- If the “search_for” argument contains more than one occurrence, the function returns the position of the first occurrence.
- If the “search_for” argument is an empty string (“”), the function returns 1.
- Since the function is case-sensitive, its better to provide cell references, instead of direct values to avoid incorrect results.
- We get the “#VALUE!” error in the following cases:
- The “search_for” is not found in the “text_to_search” argument
- The “[starting_at]” argument is zero or negative.
- The “[starting_at]” argument contains more characters than the “text_to_search” argument.
Frequently Asked Questions
We often forget in which category a function falls, here, the “FIND” function. Then, we can insert the function as follows:
Choose an empty cell 🠖 select the “Insert” tab 🠖 click the “Function” option right arrow 🠖 click the “All” option right arrow 🠖 select the “FIND” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the FIND function in Google Sheets by following the path shown below.
• Choose an empty cell 🠖 click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
• Here, click the “Functions” option 🠖 click the “All” option right arrow 🠖 select the “FIND” function, as shown below.
The FIND function in Google Sheets is will give an error for the following reasons;
a) If the “text_to_search” argument does not contain the “search_for” argument value.
b) The function is case-sensitives of value.
c) If the “[starting_at]” argument is greater than the “text_to_search” argument or smaller than or equal to 0.
The uses of the FIND function are:
a) It helps extract the relevant characters, thereby removing the unwanted substrings of a text string.
b) It helps extract the words preceding or succeeding a specific character.
c) It assists in searching the nth occurrence of a character.
d) It can be combined with other functions to find the number of times a character appears in a range.
Download Template
This article must help understand the FIND function in Google Sheets with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is FIND Function In Google Sheets. We learn how to use formula to find position of alphabet in text, example, working template. You can learn more from the following articles. –
VLOOKUP Partial Match In Google Sheets
Leave a Reply