Excel FIND Function (6 Practical Examples)

Excel FIND Function

Excel FIND Function is used to find one text string within another text string, and it returns the number of the string position of the first text string based on the first occurrence of the text string in the second string. This FIND function is case sensitive. FIND function can be used in combination with LEFT, RIGHT, and LEN.

In this section:

---

1. Syntax of FIND Function
2. FIND the position of a text string in another text
3. Extract the first Names from list of First and last Names
4. Extract the Last Names from the list of first and Last Names
5. Extract middle names separated by spaces
6. Extract middle names separated by commas
7. Extract text/numbers before first space

---

1. Syntax of FIND Function:

FIND(find_text, within_text, [start_num])

where:

• find_text: the text users want to find, and it is a REQUIRED argument.
• within_text: The text that contains the text users want to find. It is also a REQUIRED argument.
• [start_num]: As we see, this argument is within bracket and is optional. Users can specify the character at which they want to start the search. If the start_num is left empty, it assumes to be the first occurrence.

2. FIND the position of a text string within another text:

To find the position of a text string within another text string, the formula is =FIND(C6, D6), which returns the position of the text (in C6) within second text string (D6).

3. Extract First Name from First and Last Names:

To extract the first name from first and last name, the formula is =LEFT(C6, FIND(” “, C6)-1), which returns the first names.

Note: Why did we use the -1 in the formula? The purpose of using the -1 is not to include the space in the result.

4. Extract the last name from first and last names:

To extract the last name from a list of first and last names, the formula is =RIGHT(C6, LEN(C6)-FIND(” “, C6)), which returns the last names.

5. Extract the Middle Names from list of Names separated by spaces:

To extract the middle Names, the formula is =MID(C6,FIND(” “,C6)+1,FIND(” “,C6,FIND(” “,C6)+1)-1-FIND(” “, C6)), which returns the middle names.

6. Extract the Middle Names from list of Names separated by commas:

General Note: To extract the first, last, and middle names where they are separated by commas, simply replace the spaces in the formulas by commas. Let’s see an example.

In our previous example, if the names are separated by commas instead of spaces, we use the formula as =MID(C6,FIND(“,”,C6)+1,FIND(“,”,C6,FIND(“,”,C6)+1)-1-FIND(“,”, C6)).

7. Extract text/Numbers before the first space:

To extract numbers before the first space, the formula is

Note: Users can apply the same formula to extract numbers of text before the first space.

More related readings:

1. MS Office Post on FIND Function
2. Excel ARRAYTOTEXT Function
3. Excel CLEAN Function with practical examples
4. Excel CONCAT Function to join text
5. Excel REPLACE Function to replace text
6. Excel TRIM Function with Practical Examples