The FIND function returns the position (counting from the left) of the first occurrence of a character or of a text string within another text string, and it can be specified from which character the search begins. This function is case sensitive and accent sensitive.
FIND(
find_text,
within_text
[,[start_num]
[, not_found_value]]
)
- find_text: Character or string of characters to find or name of the column from which the text string to search will be extracted. Is it possible to use wildcards ? (representing a single character) and * (representing an indefinite number of characters, including the empty text string). If you want to search for a question mark or an asterisk, it must be preceded by the character ~ (a character that, on a Spanish keyboard, can be obtained by pressing the AltGr key and, without releasing it, the "4" key on the main keyboard -not the numeric one-, releasing both keys and then pressing the space bar).
- within_text: Text string in which the find_text search will be carried out or name of the column in which the search will be carried out.
- start_num: Optional argument. Initial position in within_text (counting from the left) from which the search will be carried out, ignoring the previous characters. If it is omitted, it will be considered to contain the value 1 (that is, the search will be made from the first character).
- not_found_value: Optional argument. Numeric value to return when the text string is not found, a value that will normally be -1 or 0 (values that can never be confused with those returned by the function when the searched string is found) or BLANK() .
The FIND function returns an integer representing the position of the searched text to be counted from the left (the first character occupies position 1) after ignoring the content of the characters indicated by the optional start_num argument.
The FIND function is case and accent sensitive. In this way, the following formulas:
= SEARCH("n", column_name)
= SEARCH("N", column_name )
And:
= SEARCH("a", column_name )
= SEARCH("á", column_name )
= SEARCH("à", column_name )
...will return different results, as can be seen in the following image:
(Power BI is showing the "A" in the A buscar column of the second row and the "N" in the same column of the 11th row in lowercase due to some unknown error, but both texts are in uppercase)
If you want to perform a case-insensitive search (though accent-sensitive), you should use the SEARCH function.
In the following example we want to find out the product category by extracting the position of the "UC" code that represents it, and the FIND function is used to find exactly that text, and not the texts "uc" or "Uc", for example. If the code is not found, a Blank is returned:
In this other example we want to extract the position of the letter "A" from the name, ignoring the "a" that exist in lowercase:
