Presentation of Search function
The SEARCH function requires two parameters (and a third one optional)
- The first one is characters (or the sub-string) searched
- The second is the cell containing original string
- [Optional] The start position of your search.
Example of use
Let’us use this function in an example.
You want to extract mobile numbers of cells where you have the character “/”. This symbol is the parse delimiter when you have a fix number and a mobile number is the same cell. As you can see on that picture, position of the symbol “/” is not always on the same position because of the dialing code. So the important thing is to locate the position of the character “/” in the cell in order to extract all the characters on the left of it.
Position of the symbol “/” is given by this formula.
As we want to extract the first mobile number (with area code) we will use the LEFT function and the formula in column C is:
Using of the third parameter
The third argument determines from what position you start the search.
In cell A4, you have 2 times the “/” character and you want to extract the numbers between these 2 elements. So use the MID function to extract a part of a initial string. So here, the problem is to calculate number of characters to extract which is in fact the difference between the first and the second “/” in the cell A4.
Position of the first “/”
To find the position of the second “/”; (re-used of the first formula in the third parameter plus one 😉 )
Applied to our paper, we have the following result:
The result is found with the formula MID and if you want to be sure to haven’t keep blank before or after the string, you used the TRIM function.