Excel SEARCH function
In Excel, the SEARCH function can help you to find the position of a specific character or substring from the given text string as following screenshot shown. This tutorial, I will introduce how to use this SEARCH function in Excel.
- Example 1: Search for a character or text in a text string (from the beginning)
- Example 2: Search for a character or text in a text string (with a specified beginning)
- Example 3: Find and extract the text between parentheses
The syntax for the SEARCH function in Excel is:
Return the location of the specific character or text from the given text string.
For searching a specific character or word in a text string to return its position from the first character beginning, please apply the below formula:
Note: In the above formula, B2 is the cell contains the character or text you want to find its position, A2 is the text string that you want to search from.
Then, you will get the position of the searched text from the beginning of the text string, see screenshot:
Sometime, you may want to get the position of a specific character within a text string from a specific beginning instead of the first character beginning. In this case, you just need to add the third start_num argument.
Please enter or copy the following formula into a blank cell:
Note: In the above formula, B2 is the cell contains the character or text you want to find it position, A2 is the text string that you want to search from, and C2 is the number where you want to start the search within the text string.
And then, the positions of the character or text within the text string from where you want to start the search have been returned, see screenshot:
If you have a list of text strings which some texts are enclosed with the parentheses, now, you just want to extract the text between the parentheses, here, the SEARCH function combining the MID function can help you to solve this job.
Please apply the following formula into a blank cell:
And all the texts between the parentheses have been extracted at once, see screenshot:
- Excel RIGHT Function
- RIGHT function is used to return the text from right of the text string.
- Excel NUMBERVALUE Function
- The NUMBERVALUE function help to return the real number from number is stored as text.
- Excel REPLACE Function
- The REPLACE function can help you to Find and replace characters based on given location from text string with a new text.