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 |
|---|---|
| Excellent | Spreadsheet |
| Function | Formula |
| Search | Text |
| He wasn't playing music, he was living an experience. | Data |
Sample formulas
| Formula | Description | Result |
|---|---|---|
=SEARCH("S", A1) |
Finds the position of the first occurrence of "S" in the text in cell A1. | 1 |
=SEARCH("x", A1) |
Finds the position of the first occurrence of "x" in the text in cell A1. | 2 |
=SEARCH("e", A1, 3) |
Finds the position of the first occurrence of "e" in the text in cell A1, starting from the 3rd character. | 4 |
=SEARCH(", ", A4) |
Finds the position of the comma in the text in cell A4. | 24 |
=SEARCH("e", A1:A4) |
Finds the position of the first occurrence of "e" in the text in cell range A1:A4. | #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.
Related functions
CHAR
CODE
CONCATENATE
CONCATENATEIF
FIND
LEFT
LEN
LOWER
MID
PROPER
REPT
RIGHT
SEARCH
SUBSTITUTE
TEXT
TEXTJOIN
TRIM
UNICHAR
UNICODE
UPPER