FIND

Return character position of a text in Excel
Return character position in a text.

Rational

Find the letter’s position in a text.

Description

The Excel FIND function displays the numerical location of the character in a given text. The starting position of a String can change based on the third argument [Start_num]. Check the video tutorial on the Excel Find function.

For example, to find the 2nd occurrence of 8, =FIND(8,128128,4) returns 6, start finding 8 from 4th digit that returns 6, i.e., 6th position in the figure.

Syntax

= FIND(find_text, within_text, [start_num])

Parameters

find_text – The character to find
within_text – The string containing the character
start_num [Optional] – Specifies the character to start the search.

Return Value

The boolean result (True or False)

Key Notes

  • In the FIND function, the first character in the within_text argument is character number 1 (default position).
  • The FIND function is case-sensitive but allow wildcard characters.
  • Use the SEARCH excel function if a user wants to perform a non-case-sensitive search.

The FIND function returns the #VALUE! error value in case

  1. start_num is not greater than zero
  2. start_num is greater than the length of within_text
  3. find_text does not appear in within_text
  4. Case Sensitive

Let’s check out examples:

  • If you want to locate a character or alphabet in a string without worrying about the index, you don’t need to fill ‘start_num’ arguments like in example 1.
  • You can also find wild or special character or values index in the word.
  • In case you want to find the occurrence or sequence that is after 1st occurrence, then you can merge two formulas,

Like if you’re going to see the 2nd, last or nth more event, then you can use =FIND(B10,C10, (FIND(B9,C9,D9)+1 or nth position)). The formula returns the same character position but with 2nd or different stances. (Download the workbook for clarity).

  • You can also find more than one character’s location using the function by inputting in the first argument like special characters before and after a value or text. 
    • Note: start_num [Optional] is important to return the place and to find the Nth position.

Keep Reading Similar Functions:

Count a number of characters in a text string in Excel

LEN

The Excel LEN function counts a number of characters of a given text string excluding number formatting. For example, LEN(“Excel”) returns “5”.

Read More »
Finds position of one text value within another Text in Excel

SEARCH

The SEARCH function finds the position of a specified character or sub-string within a text string. I.e., =SEARCH(“ring”,”String”,1) return “3”, “ring” begins at the third character of “String”.

Read More »
Match two text strings if they are exactly same in Excel

EXACT

The Excel EXACT function display (True or False) after matching two text strings if they are exactly same. Also identifies case-sensitive differences between two text.

Read More »