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 |
|
|---|---|---|---|
1 |
Excel | Spreadsheet | |
2 |
Function | Formula | |
3 |
Exa4mple | Text | |
4 |
345566 | Sample Data |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Find the starting position of a specific character within a text string, matching case exactly. | =FIND("S", B1) |
Finds where the uppercase "S" first appears in "Spreadsheet" — right at the beginning. Result: 1 |
| Find the position of a lowercase letter within a text string. | =FIND("p", B1) |
Finds where the lowercase "p" first appears in "Spreadsheet". Result: 2 |
| Search for a letter starting partway through a text string, skipping any earlier matches. | =FIND("e", B1, 2) |
Starts searching for "e" from the 2nd character of "Spreadsheet" onward, so the "e" at position 4 is the first one found. Result: 4 |
| Find the starting position of a two-letter substring within a word. | =FIND("un", A2) |
Finds where "un" first appears in "Function". Result: 2 |
| Find the position of a number embedded within a text string. | =FIND(4, A3) |
Finds where the digit "4" first appears in "Exa4mple". Result: 4 |
| Find the position of a digit within a number that's stored as text. | =FIND(4, A4) |
Finds where the digit "4" first appears in "345566". Result: 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.