Description
Use this function to locate a text string or number within a text string or number, 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, especially when case sensitivity is important.
Syntax
FIND(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 reference to a cell containing the text string. |
within_text |
Yes | The text in which you want to search for the find_text. |
A text string or a reference to a cell containing the text string. |
start_num |
No | The character number in the within_text at which to start the search. |
A positive integer, a reference to a cell containing a positive integer, or a formula which results in either of these. If omitted, it is assumed to be 1. |
Example
Sample data
| A | B |
|---|---|
| Excel | Spreadsheet |
| Function | Formula |
| Exa4mple | Text |
| 345566 | Sample Data |
Sample formulas
| Formula | Description | Result |
|---|---|---|
=FIND("S", B1) |
Finds the position of the first occurrence of "S" in the text in cell B1. | 1 |
=FIND("p", B1) |
Finds the position of the first occurrence of "p" in the text in cell B1. | 3 |
=FIND("e", B1, 2) |
Finds the position of the first occurrence of "e" in the text in cell B1, starting from the 2nd character. | 2 |
=FIND("un", A2) |
Finds the position of the start of "un" in the text in cell A2. | 3 |
=FIND(4, A3) |
Finds the position of the first occurrence of "4" in the text in cell A3. | 4 |
=FIND(4, A4) |
Finds the position of the first occurrence of "4" in the text in cell A4. | 2 |
Notes
- FIND works from left to right.
- FIND doesn't support cell ranges.
- FIND is case-sensitive. To perform a case-insensitive search, use SEARCH.
- FIND doesn't support wildcards. For wildcard support, use SEARCH.
- If
find_textis not found withinwithin_text, FIND returns a #VALUE! error. - If
find_textis text and not in quotation marks, FIND returns a #NAME! error. Numerals do not require quotation marks. - FIND 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 FIND to locate the position of a substring within a text string when case sensitivity is important.
- Combine FIND with LEN to find the length of text before or after a certain substring.
- FIND 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