FIND
Returns the position of one text value inside another, matching case exactly.
FIND(<find_text>, <within_text> [, <start_num>] [, <not_found_value>])Returns: A whole number (a scalar). An error, or the not found value, when the text is absent.
What it does
FIND returns the position of one text value inside another. It is the case-sensitive version of SEARCH: "ACH" is found in "ACH Payment" but not in "ach payment". Use FIND when the capitals carry meaning, such as a code that is written in a particular case, or when you want exact control of a position for MID or LEFT.
Syntax and arguments
- find_text: the text to look for.
- within_text: the text to search in.
- start_num (optional): the position to start from. The default is 1.
- not_found_value (optional): what to return when the text is missing. Without it, a missing text is an error.
Example
Vendor payments made by ACH are marked "ACH" in the bank description, in capitals, by the bank's own system. The payment type must be read exactly.
Is ACH Payment =
IF(
ISNUMBER(FIND("ACH", BankLines[Description])),
1,
0
)
Descriptions with the capital letters "ACH" are flagged. A lower case "ach" in a customer's note is not flagged, which is the reason to use FIND rather than SEARCH here.
Example: a second, different use
Find the position of the separator in a code such as "CC042/2026", then take the part after it with MID:
Separator Position = FIND("/", Journal[CostCode], 1, 0)
Position 6 tells MID where the year starts. The not found value of 0 avoids an error when the separator is missing.
Things to watch
- FIND is case sensitive. "ACH" and "ach" are different.
- Without a not found value, a missing text returns an error, so supply 0 or use ISNUMBER.
- Positions start at 1. A result of 0 from a not found value is not a real position.
- For a case blind test, SEARCH is usually simpler.
In business terms
FIND is used when a code or a flag is written in a fixed case, such as payment types from a bank, document codes from an ERP or account type markers from a legacy system. The exact match means the flags are the ones the source system meant.