# Find the position of a character in a string with SEARCH function Last Updated on 10/11/2023

The SEARCH function allows you to find the position of a specific character in a string

1. The first argument is the lookup character

It could be one character or many

2. The second argument is the string

You can write a string or use the cell reference

3. [Optional] The starting position

By default, the value is 1 (beginning of the string) but you can indicate the value of the starting position

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

## When using the function SEARCH

You want to extract the mobile numbers in column A. The mobile numbers are in the string after the character "/". As you can see in this picture, the position of the symbol "/" is not always in the same position. Sometimes it's because of the dialing code, and sometimes because there are spaces around the "/" symbol.

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

The position of the symbol "/" is given by this formula.

=SEARCH("/",A2)

So for the first phone number, the first "/" is in the 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))

### 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)

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),"")

## Example 2: Extract the mobile phone

Watch out! The position returned 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:" has 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),"") ## Frédéric LE GUEN

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

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

Not

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

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

Good catch 😉

# Find the position of a character in a string with SEARCH function

Last Updated on 10/11/2023

The SEARCH function allows you to find the position of a specific character in a string

1. The first argument is the lookup character

It could be one character or many

2. The second argument is the string

You can write a string or use the cell reference

3. [Optional] The starting position

By default, the value is 1 (beginning of the string) but you can indicate the value of the starting position

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

## When using the function SEARCH

You want to extract the mobile numbers in column A. The mobile numbers are in the string after the character "/". As you can see in this picture, the position of the symbol "/" is not always in the same position. Sometimes it's because of the dialing code, and sometimes because there are spaces around the "/" symbol.

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

The position of the symbol "/" is given by this formula.

=SEARCH("/",A2)

So for the first phone number, the first "/" is in the 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))

### 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)

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),"")

## Example 2: Extract the mobile phone

Watch out! The position returned 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:" has 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),"")

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

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

Not

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

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

Good catch 😉