Home » Text functions » Function SEARCH

Function SEARCH

Reading time: 3 minutes
Last Updated on 30/08/2021 by Frédéric LE GUEN

Presentation of function SEARCH

The function SEARCH allows you to find the position of a symbol into a string

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.

=SEARCH(Character to find, String to look into, Position to start)

When use the function SEARCH

Let's see how this function works in the following example

You want to extract the mobile numbers in the column A. The mobile numbers are in the string after the character "/".

As you can see on this picture, the position of the symbol "/" is not always on the same position . Sometimes it's because of the dialing code, sometimes because there is spaces.

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.

Find the position of a character

Position of the symbol "/" is given by this formula.

=SEARCH("/",A2)

SEARCH returns the position of a character in a string

So for the first phone number, the first "/" is in 9th position in the string

Example 1: Extract the first phone number

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,SEARCH("/",A2))

The function SEARCH helps to create dynamic extract with the LEFT function
The function SEARCH helps to create dynamic extract with the LEFT function

Extract the position of the mobile number

The mobile number is after the string "cell:". So now, we are going to write a new SEARCH function with this specific string

=SEARCH("cell:",A2)

The SEARCH function returns an error

As you can see, for the last string, there is an error. This error occurs when the string is not found.

Use the function IFERROR

To avoid returning an error, we can use the wonderful function IFERROR

=IFERROR(formula, result in case of error)

So here, we can simply write

=IFERROR(SEARCH("cell:",A2),"")

The function IFERROR returns empty when SEARCH returns an error

Example 2: Extract the mobile phone

Watch out! The position returns by the function is the position of the first character, not the last one ❗❗❗

So we must add to the result of the function the value 5. The string "cell:" as 5 characters

=IFERROR(SEARCH("cell:",A2)+5,"")

Example 3: Extract the last phone number

To extract the last phone number, we will write the following formula

=IFERROR(RIGHT(A2,LEN(A2)-D2),"")

2 Comments

  1. Nick
    06/08/2021 @ 10:26

    Example 1 should be: =LEFT(A2,SEARCH("/",A2))

    Not

    =LEFT(A2;SEARCH("/",A2))

    Reply

    • Frédéric LE GUEN
      30/08/2021 @ 13:31

      Good catch 😉

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Function SEARCH

Reading time: 3 minutes
Last Updated on 30/08/2021 by Frédéric LE GUEN

Presentation of function SEARCH

The function SEARCH allows you to find the position of a symbol into a string

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.

=SEARCH(Character to find, String to look into, Position to start)

When use the function SEARCH

Let's see how this function works in the following example

You want to extract the mobile numbers in the column A. The mobile numbers are in the string after the character "/".

As you can see on this picture, the position of the symbol "/" is not always on the same position . Sometimes it's because of the dialing code, sometimes because there is spaces.

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.

Find the position of a character

Position of the symbol "/" is given by this formula.

=SEARCH("/",A2)

SEARCH returns the position of a character in a string

So for the first phone number, the first "/" is in 9th position in the string

Example 1: Extract the first phone number

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,SEARCH("/",A2))

The function SEARCH helps to create dynamic extract with the LEFT function
The function SEARCH helps to create dynamic extract with the LEFT function

Extract the position of the mobile number

The mobile number is after the string "cell:". So now, we are going to write a new SEARCH function with this specific string

=SEARCH("cell:",A2)

The SEARCH function returns an error

As you can see, for the last string, there is an error. This error occurs when the string is not found.

Use the function IFERROR

To avoid returning an error, we can use the wonderful function IFERROR

=IFERROR(formula, result in case of error)

So here, we can simply write

=IFERROR(SEARCH("cell:",A2),"")

The function IFERROR returns empty when SEARCH returns an error

Example 2: Extract the mobile phone

Watch out! The position returns by the function is the position of the first character, not the last one ❗❗❗

So we must add to the result of the function the value 5. The string "cell:" as 5 characters

=IFERROR(SEARCH("cell:",A2)+5,"")

Example 3: Extract the last phone number

To extract the last phone number, we will write the following formula

=IFERROR(RIGHT(A2,LEN(A2)-D2),"")

2 Comments

  1. Nick
    06/08/2021 @ 10:26

    Example 1 should be: =LEFT(A2,SEARCH("/",A2))

    Not

    =LEFT(A2;SEARCH("/",A2))

    Reply

    • Frédéric LE GUEN
      30/08/2021 @ 13:31

      Good catch 😉

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *