Excel Tip: The Curious Case of FIND and SEARCH

From the Not Just Numbers blog:

One of the things I love about Excel is that I continue to learn and find new quirks all the time.

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”.