When working with messy text data in Google Sheets—such as extracting a specific product code from a long string, or separating a first name from a last name—you first need to know exactly where a specific character or word is located within that string.
Google Sheets provides two incredibly useful functions for this exact purpose: SEARCH and FIND. While they seem identical, there is one crucial difference in how they operate regarding case sensitivity.
In this guide, you will learn how to use the SEARCH and FIND functions to locate substrings, and when to choose one over the other.
The Core Difference: Case Sensitivity
Both functions return the numerical position of a specific character (or string of characters) within a larger cell of text.
- FIND is Case-Sensitive: If you tell FIND to look for a capital “A”, it will completely ignore lowercase “a”.
- SEARCH is Case-Insensitive: If you tell SEARCH to look for an “A”, it will stop at the very first “a” it sees, regardless of capitalization. It also supports wildcards (like
*or?), whereas FIND does not.
As a general rule, use SEARCH for most tasks, unless you specifically need to isolate capitalized letters (like an ID code).
The Syntax
Both functions use the exact same three arguments:
=SEARCH(search_for, text_to_search, [starting_at])=FIND(search_for, text_to_search, [starting_at])- search_for: The character, word, or symbol you are looking for (must be in quotation marks).
- text_to_search: The cell you are scanning.
- starting_at (optional): The character number where Google Sheets should start looking. Defaults to 1 (the beginning).
Use Case 1: Finding the Space in a Name
Imagine you have a list of full names in Column A (e.g., A2 is “Jane Smith”). You want to extract just the first name into Column B. To do this, you must first tell Google Sheets exactly where the space character is located.
In cell B2, type:
=SEARCH(" ", A2)The formula looks at “Jane Smith”, counts the characters (“J” is 1, “a” is 2, “n” is 3, “e” is 4, space is 5), and outputs the number 5.
You can then combine this with the LEFT function to automatically extract everything before that space: =LEFT(A2, SEARCH(" ", A2) - 1). This outputs “Jane”.
Use Case 2: Using the ‘starting_at’ Argument
If you are trying to find the second instance of a character in a string, the optional third argument is vital.
Suppose cell A2 contains the text: product_code_123. You want to find the position of the second underscore.
If you just run =SEARCH("_", A2), it will output 8 (the first underscore).
To find the second one, you must tell the function to start searching after the first one. You can nest a SEARCH function inside another SEARCH function:
=SEARCH("_", A2, SEARCH("_", A2) + 1)The inner SEARCH finds the first underscore (8). Adding 1 tells the outer SEARCH to start looking from character 9. It then finds the second underscore and outputs 13.
By mastering SEARCH and FIND, you lay the foundation for advanced text parsing, allowing you to clean and organise messy datasets with precision.