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.
FIND Function
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.
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
FIND Syntax
The FIND function has the following syntax:
- FIND(find_text,within_text,[start_num])
- 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
FIND Traps
- 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.
=FIND(B5,B2)
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).
=IFERROR(FIND(B5,B2),”Not Found”)
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
=IF(OR(ISNUMBER(FIND($E$2:$E$4,B2))),”Yes”,”No”)
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.
=IF(ISNUMBER(–LEFT(B2,1)),MID(B2,FIND(” “,B2)+1,LEN(B2)),B2)
Download the FIND Function File
To see the formulas used in today’s examples, go to the FIND Function page on my Contextures website.
The zipped Excel file is in xlsx file format, and does not contain any macros.
Watch the FIND Video
To see a demonstration of the examples in the FIND function sample workbook, watch this short Excel video tutorial – Locate Text With Excel FIND Function
_____________
One thought on “30 Excel Functions in 30 Days: 23 – FIND”