Description
Use this function to find one text string within another text string, and return the number of the starting position of the first text string from the first character of the second text string. Supported in Chains.
This function is useful for determining the position of a specific character or substring within a larger string.
Syntax
SEARCH(find_text, within_text, [start_num])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
find_text |
Yes | The text you want to find. | A text string or a cell reference containing the text string. |
within_text |
Yes | The text in which you want to search for find_text. |
A text string or a cell reference containing the text string. |
start_num |
No | The character number in within_text at which to start the search. |
A positive integer. If omitted, it is assumed to be 1. |
Example
Sample data
| A | B | ||
|---|---|---|---|
| 1 | Excellent | Spreadsheet | |
| 2 | Function | Formula | |
| 3 | Search | Text | |
| 4 | He wasn't playing music, he was living an experience. | Data |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Find the starting position of a specific letter within a text string. | =SEARCH("S", B1) |
Finds where "S" first appears in "Spreadsheet" — right at the beginning. Result: 1 |
| Find the position of a letter within a text string. | =SEARCH("x", A1) |
Finds where "x" first appears in "Excellent". Result: 2 |
| Search for a letter starting partway through a text string, skipping any earlier matches. | =SEARCH("e", A1, 3) |
Starts searching for "e" from the 3rd character of "Excellent" onward, so the earlier "e" isn't considered. Result: 4 |
| Find the position of a delimiter, like a comma, within a longer text string. | =SEARCH(", ", A4) |
Finds where the comma-and-space combination first appears in the sentence in A4. Result: 24 |
| See what happens when you try to search across a range of cells instead of a single cell. | =SEARCH("e", A1:A4) |
SEARCH doesn't support cell ranges, so supplying one returns an error instead of a position. Result: #VALUE! |
Notes
- SEARCH allows the use of wildcards: "?" to match any single character and "*" to match any sequence of characters.
- SEARCH is case-insensitive. To perform a case-sensitive search, use FIND.
- SEARCH doesn't support cell ranges. If a range is supplied, SEARCH returns the #VALUE! error.
- If the value in
find_textis not found inwithin_text, SEARCH returns the #VALUE! error. - SEARCH can be combined with other functions such as MID, LEFT, or RIGHT to extract specific parts of a text string based on the position found.
Tips
- Use SEARCH to locate the position of a substring within a text string when the case is not important.
- Combine SEARCH with LEN to find the length of text before or after a certain substring.
- SEARCH can help in data cleaning and parsing tasks by identifying the position of delimiters or specific characters.