FIND Function In Excel

This tutorial demonstrates how to use the Excel FIND Function in Excel to find text within text.

FIND Main Function

FIND Function Overview

The FIND Function Finds a character or text string and returns it’s position in the cell. Case-sensitive.

To use the FIND Excel Worksheet Function, select a cell and type:

find formula syntax

(Notice how the formula inputs appear)

FIND function Syntax and inputs:

find_text – The string of text that you wish to find.

within_text – The original string of text.

start_num – OPTIONAL. The character number from which to start the search. Note: This only changes the search range, it will not affect the number returned when a match is found. Example: searching for “s” in string “suns” will return 1 by default, but if the start_num is set to 2, it will return 4.

What Is the FIND Function?

The Excel FIND Function tries to find string of text within another text string. If it finds it, FIND returns the numerical position of that string.

FIND is case-sensitive. So, “text” will NOT match “TEXT”. For case-insensitive searches, use the SEARCH Function <<link>>.

 

How to Use the FIND Function

To use the Excel FIND Function, type the following:

elephant

In this case, Excel will return the number 1, because “e” is the first character in the string “elephant”.

Let’s take a look at some more examples:

Start Number (start_num)

The start number tells FIND what numerical position in the string to start looking from. If you don’t define it, FIND will start from the beginning of the string.

No Start Num

 

Now let’s try defining a start number of 2. Here, we see that FIND returns 3. Because it starts looking from the second character, it misses the first “e” and finds the second:

Start Num

Start Number (start_num) Errors

If you want to use a start number, it must:

  • be a whole number
  • be a positive number
  • be smaller than the length of the string you are looking in
  • not refer to a blank cell, if you define it as a cell reference

Otherwise, FIND will return a #VALUE! error as shown below:

Start Num Errors

Unsuccessful Searches Return a #VALUE! Error

If FIND does not locate the string you’re looking for, it will return a value error:

Unsuccessful Search

FIND is Case-Sensitive

In the example below, we’re searching for “abc”. FIND returns 10 because it is case-sensitive – it ignores “ABC” and the other variations:

Case Sensitive

 

FIND Does Not Accept Wildcards

You cannot use wildcards with FIND. Below, we’re looking for “?000”. In a wildcard search, this would mean “any character followed by three zeroes”. But FIND takes this literally to mean “a question mark followed by three zeroes”:

Wildcard 1

The same applies to the asterisk wildcard:

Wildcard 2

 

Instead, to search text with wildcards, you can use the SEARCH Function:

Wildcard 1

Wildcard 02

How to Split First and Last Names from a Cell with FIND

If your spreadsheet has a list of names with both the first and last names in the same cell, you might want to split them out to make sorting easier. FIND can do that for you – with a little help from some other functions.

Getting the First Name

The LEFT Excel function returns a given number of characters from a string, starting from the left.

We can use it to get the first name, but since names are different lengths, how do we know how many characters to return?

Easy – we just use FIND to return the position of the space between the first and last name, subtract 1 from that, and that’s how many characters we tell LEFT to give us.

The formula looks like this:

First Name

Getting the Last Name

The RIGHT Excel function returns a given number of characters from a string, starting from the right.

We have the same problem here as with the first name, but the solution is different, because we have to get the number of characters between the space and the right edge of the string, not the left.

To get that, we use FIND to tell us where the space is, and then subtract that number from the total number of characters in the string, which the LEN Function can give us.

The formula looks like this:

Last Name

If the name contains a middle name, note that it will be split into the last name cell.

Finding the nth Character in a String

As noted above, FIND returns the position of the first match it finds. But what if you want to find the second occurrence of a particular character, or the third, or fourth?

This is possible with FIND, but we’ll need to combine it with a couple of other functions: CHAR and SUBSTITUTE.

Here’s how it works:

  • CHAR returns a character based on its ASCII code. For example, =CHAR(134) returns the dagger symbol.
  • SUBSTITUTE goes through a string and lets you swap out a character for any other one.
  • With SUBSTITUTE you can define an instance number, meaning it can swap the nth occurrence of a given string for anything else.
  • So, the idea is, we take our string, use SUBSTITUTE to swap the instance of the character we want to find for something else. We’ll use CHAR to swap it for something that is unlikely to be found in the string, then use FIND to locate that obscure substitute.

The formula looks like this:

And here’s how it works in practice:

FIND the nth Character

FIND Vs SEARCH

FIND and SEARCH are very similar – they both return the position of a given character or substring within a string. However, there are some differences:

  • FIND is case sensitive but SEARCH is not
  • FIND does not allow wildcards, but SEARCH does

You can see a few examples of these differences below:

FIND vs SEARCH

FIND in Google Sheets

The FIND Function works exactly the same in Google Sheets as in Excel:

Search G Function

Additional Notes

The FIND Function is case-sensitive.

The FIND Function does not support wildcards.

Use the SEARCH Function to use wildcards and for non case-sensitive searches.

FIND Examples in VBA

You can also use the FIND function in VBA. Type:
application.worksheetfunction.find(find_text,within_text,start_num)
For the function arguments (find_text, etc.), you can either enter them directly into the function, or define variables to use instead.

Return to the List of all Functions in Excel