An issue that arose while working on a spreadsheet for a client last week, caused me to find one of these such quirks.
Read on about the Curious Case of FIND and SEARCH...
I regularly use the FIND function to find text within other text, for various reasons.
The FIND function works as follows:
=FIND(text,within text,[start character])
This function returns the position (character number) of the first occurrence of "text" within "within text" - reading left to right, starting at the start character, or the beginning if omitted.
So,
=FIND("U","NOTJUSTNUMBERS") returns 5
whereas,
=FIND("U","NOTJUSTNUMBERS",7) returns 9
I had used this function as part of a larger formula in a client spreadsheet, but it needed to be case-insensitive. After a little Googling, I discovered the SEARCH function which is exactly the same as the FIND function, the only difference being that it is not case sensitive!
I was even able to use Find and Replace to swap all of my FIND functions for SEARCH functions, to stop them being case sensitive.
I might have come across this earlier if it had been called something like FINDCASEINSENSITIVE!
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".
Similarly with SUBSTITUTE, which is case sensitive; the only way to make it not so would be to use it with UPPER/LOWER or nest substitutions
ReplyDeleteeg SUBSTITUTE("My Name Is Glen","n","e") would return "My Name Is Glee"
but SUBSTITUTE(SUBSTITUTE("My Name Is Glen","n","e"),"N","E") or
PROPER(SUBSTITUTE(LOWER("My Name Is Glen"),"n","e")) would be needed to return "My Eame Is Glee"
btw REPLACE does not do what you think it does (if you thought it were similar): that only replaces characters at a specified length along a string
Jim
Thanks for the input Jim. UPPER and LOWER (and PROPER) can be very useful when manipulating text!
ReplyDeleteThe Replace I mentioned was the Find and Replace feature from the Home Ribbon, rather than the function.