## Tuesday, 1 September 2015

### Excel Tip: The Curious Case of FIND and SEARCH

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.

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

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

eg 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

2. Thanks for the input Jim. UPPER and LOWER (and PROPER) can be very useful when manipulating text!

The Replace I mentioned was the Find and Replace feature from the Home Ribbon, rather than the function.