Yesterday, in the 30XL30D challenge, we had a light day, with the N function, and learned that can return a number, based on a value.
For day 23 in the challenge, we'll examine the FIND function. It's similar to the SEARCH function, which we saw on Day 18, but the FIND function is case sensitive.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file -- the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let's take a look at the FIND information and examples, and if you have other tips or examples, please share them in the comments.
Function 23: FIND
The FIND function finds a text string, within another text string, and is case sensitive.
How Could You Use FIND?
The FIND function can find text within another string, and is case sensitive. For example:
- Find start position of text in text string
- Find exact values from a list
- Find street name in address
The FIND function has the following syntax:
- find_text is the text that you're looking for.
- within_text is the string that you're searching in.
- if start_num is omitted, the search starts with the first character
- The FIND function will return the position of the first matching string, and it is case sensitive. For a search that is not case sensitive, use the SEARCH function, which we saw later earlier the 30XL30D challenge.
- You can't use wildcard characters in the find_text string. For wildcards, use the SEARCH function.
Example 1: Find Text in a Text String
To find specific text in a text string, you can use the FIND function. It is case sensitive, so in the screen shot below, the first two "i" characters are ignored, because they are lower case.
To handle errors, if the text is not found, you can wrap the FIND function with IFERROR (in Excel 2003 or earlier, use IF and ISERROR).
Example 2: Find exact values from a list
Because FIND is case sensitive, you can use it to find exact strings in another string. In this example, there are valid codes listed in column E. With the FIND function, we can identify the ID numbers in column B that contain one of the valid code strings.
This formula must be array-entered, by pressing Ctrl + Shift + Enter
Example 3: Find street name in address
In the next example, most of the addresses in column B start with a street number. With the formula in column C, we check for a number in the first character. If it's a number, the FIND function locates the first space character, and the MID function returns all the text from the next character, to the end.
Download the FIND Function File
To see the formulas used in today's examples, you can download the FIND function sample workbook. The file is zipped, and is in Excel 2007 file format.
Watch the FIND Video
To see a demonstration of the examples in the FIND function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Locate Text With Excel FIND Function