30 Excel Functions in 30 Days: 23 – FIND

Locate Text With Excel FIND Function

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.

Find00

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

Find Text in a Text String with FIND function
Find Text in a Text String with FIND function

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

Find02

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)

Find03

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”

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.