Excel FIND Function – Get Position of Text in String

In one of our previous blogs, we had learned how to use the Find and Replace dialog box in excel to find a cell containing a particular string and/or replace its value. Sometimes you may have a need to find the position of a particular text/string in another string in a particular cell. To achieve this, there are two inbuilt functions provided in Microsoft excel. They are the FIND function and the SEARCH function in excel.

In this tutorial, we would unlock how to use the FIND formula in excel along with examples.

Here we go.

When to Use FIND Function in Excel

The FIND excel function enables one to extract the position of a particular string or text in a cell. This function is part and parcel of the text function group.

It is important to note that the excel FIND formula is a case-sensitive formula. This means that it also validates the upper and lower case while finding the position of a string in an excel cell.

If you want to perform case-insensitive search, use the SEARCH function in Excel.

Syntax and Arguments

=FIND(find_text, within_text, [start_num])

The FIND excel formula has three arguments as described below:

  • find_text – In this argument, specify the text or string for which you want to find the position.
  • within_text – In this argument, specify the text or string within which you want to search for the find_text.
  • [start_num] – This is an optional argument. In this argument, specify the position from which the excel should start searching.

Example – FIND Function in Excel

In this section of the blog, we would learn how the FIND function work in Excel with the help of examples.

The below image contains the word ‘Excel Unlocked’ in column A. Let us find the position of E (in upper case) and e (in lower case) using the FIND excel function.

FIND function - Example

To find the position of ‘E’, use the following formula in cell C2.

=FIND("E",A2)

AND

=FIND("e",A2)
Using FIND Function in Excel with Examples

Explanation – In the above example, the first formula will return the position of capital ‘E’ in the text in cell A2, which is at the 1st position. Similarly, the second formula will return the position of the small letter ‘e’, which is at the 4th position.

You may also give reference to cells B2 and B3 like this:

Using cell reference in FIND function

From the above example, it is clear that if you keep the [start_num] argument as blank, the FIND formula by default returns the first occurrence of the find_text, and ignores the next one.

infographics find function in excel

Suppose you want to find the position of second ‘e’, use the [start_num] argument, as below:

=FIND(B2,A2,5)

As a result, excel will return the position value as 13 (Excel Unlocked).

Using start_num in FIND function

Similarly, you can find the position of a text having more than one letter, like this:

Find Position of Long Text in Excel

In that case, the FIND function will return the position of the first character of find_text (in the above example, is dot).

Do Not Miss These Points

  • The excel FIND formula is a case-sensitive formula. Use the SEARCH function for a case-in-sensitive search of string.
  • The FIND function will return a #VALUE error,
    • if it does not find the find_text in the within_text or
    • when the [start_num] value is more than the within_text length.
#VALUE error reason - FIND Excel function
  • When find_text contains multiple characters, the FIND function will return the position of the first character (already covered in the example section above).
  • When there are multiple occurrences of the find_text, the FIND function will return the position of the first occurrence, by default (unless you specify [start_num]).
  • The result of a blank find_text (“”) is 1, as shown below:
find_text as blank in FIND function

Thank you.

Leave a Comment