↑ Return to Text functions

SEARCH Function

The function allows you to search the position of a symbol into a string in a cell.

 

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. function_SEARCHSo 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.

=SEARCH(“/”,A2)

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:

=LEFT(A2;RESEARCH(“/”,A2))

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 “/”

=SEARCH(“/”, A2)

To find the position of the second “/”; (re-used of the first formula in the third parameter plus one 😉 )

=SEARCH(“/”;A2;RESEARCH(“/”,A2) +1)

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.

=TRIM(MID(A4,B4+1,D4-B4-2))

 

1 comment

  1. Burak

    What could I do if there would be 3 times the “/” character and I ‘d like to extract the numbers between 2nd and 3rd?

Leave a Reply

%d bloggers like this: