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:
- Syntax of SEARCH Function
- Return the position of a text
- Extract text before first space with SEARCH and LEFT
- Test whether a cell contains a text
- Replace text with another one by search
- Using Array with SEARCH Function to extract first name
- 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:
- MS office post on SEARCH Function
- Extract first Name in excel (3 practical examples)
- Extract Middle names (3 practical examples)
- Excel ARRAYTOTEXT Function
- Excel CLEAN Function with practical examples
- Excel CONCAT Function to join text
- Excel REPLACE Function to replace text
- Excel TRIM Function with Practical Examples
- Get Name from email address
You got a very superb website, Sword lily I discovered it through yahoo.