Excel SEARCH Function (6 Practical Examples)

Excel SEARCH Function

The excel SEARCH function (SEARCHB) is used to locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string. For example, =SEARCH(“p”, “Apple”) returns 2 because the first occurrence of p is in the 2nd position.

In this section:

---

1. Syntax of SEARCH Function
2. Return the position of a text
3. Extract text before first space with SEARCH and LEFT
4. Test whether a cell contains a text
5. Replace text with another one by search
6. Using Array with SEARCH Function to extract first name
7. Difference between FIND and SEARCH

---

1. Syntax of SEARCH Function:

SEARCH(find_text,within_text,[start_num])

where:

• find_text : The text that users want to find. It is a Required argument.
• within_text : The text in which users want to search for the value of the find_text argument. It is also a Required argument.
• start_num: The character number in the within_text argument at which users want to start searching. [Optional]

2. Return the position of a text:

To find the position of a text in another text, the formula is =SEARCH(C6, D6), which returns the position of a certain text in another text.

3. Extract text before first space with SEARCH and LEFT:

To text before first space with LEFT and SEARCH, the formula is =LEFT(C6, SEARCH(” “, C6)), which returns text before first space.

4. Test whether a cell contains a text:

To test whether a cell contains a text, the formula is =ISNUMBER(SEARCH(D6, C6)), which returns TRUE if specific text is in the cell; otherwise it returns FALSE.

5. Replace text with another one by search:

To replace text by searching, the formula is =REPLACE(C6,SEARCH(D6,C6),6,”Amount”).

6. Using Array with SEARCH Function:

To find the first name from a list of names, users can apply array formula as {=LEFT(C8:C14, SEARCH(” “, C8:C14,1)-1)}, which returns the first name.

7. Difference between FIND and SEARCH:

The differences between FIND and SEARCH Functions are as follows:

More related readings:

1. MS office post on SEARCH Function
2. Extract first Name in excel (3 practical examples)
3. Extract Middle names (3 practical examples)
4. Excel ARRAYTOTEXT Function
5. Excel CLEAN Function with practical examples
6. Excel CONCAT Function to join text
7. Excel REPLACE Function to replace text
8. Excel TRIM Function with Practical Examples
9. Get Name from email address