While working in Excel we're always looking for something. The FIND function can be very helpful in our searches.
Remember two things: it is case-sensitive (differentiates between "a" and "A") and doesn't work with date format (cells formatted as dates)
Used by itself, it just finds position of a character or text within a longer string. However, when used in combination with some other functions, it can be very useful in solving complex tasks. Here is summary of some examples of Find usage, described beneath the table.
Example 1: Find the directory/folder name of the file, you are using right now, with this formula:
=IF(CELL("Filename",A1)>"",LEFT(CELL("Filename",A1),FIND("[",CELL("Filename",A1))-1),"")
Example 2: Extract some number of characters (e.g. 4) following the second occurrence of "/": =MID(A1,FIND("/",A1,FIND("/",A1,1)+1)+1,4)
Example 3: Return all characters between 2nd and 3rd "%":
=MID(A1,FIND("%",A1,FIND("%",A1)+1)+1,FIND("%",A1,FIND("%",A1,FIND("%",A1)+1)+2)-FIND("%",A1,FIND("%",A1,+1)-1)
Example 4: Find position of any of (2nd,3rd,4th...) occurrences of a given character in a string (e.g. 3rd occurrence of "e"):
=FIND(CHAR(10),SUBSTITUTE(A1,"e",CHAR(10),3))
Example 5: Find some substring of the string after a given character (e.g. after ">"):
=RIGHT(A1,LEN(A1)-FIND(">",A1))
Example 6: Find position of the second occurrence of some character in a string (e.g. "S"):
=FIND( "S",A1,FIND("S",A1,1)+1)
Example 7: Find text between two characters (can be the same or different); in this example, between "$" and "£":
=MID(A1,FIND("$",A1)+1,FIND("£",A1)-FIND("$",A1)-1)
