Excel Search Function: A Quick Guide

[nerdslife_team]

Alright everyone, let’s talk about the SEARCH function in Excel. It’s a handy tool for finding text within other text, and it returns the position of that text as a number. Basically, it tells you where a specific word or character starts in a cell.

For example, if you’re trying to find where “son” starts within “Jackson,” the SEARCH function would return 5. It always gives you the starting position of the text you’re searching for.

The SEARCH function isn’t usually used on its own. It shines when combined with other functions like FIND, MID, LEFT, and ISNUMBER. In this post, we’ll break down how to use SEARCH, and how it works with other Excel functions.

Excel Search Function Explained

The SEARCH function is all about locating a string or a single character within another string of text. When it finds a match, it returns the position number where the searched text begins within that string.

Syntax of the SEARCH Function

=SEARCH (find_text, within_text, [start_num])

• find_text (required) is the character or text string you’re looking for.
• within_text (required) is the text string where you’re doing the search. It’s often a cell reference, but it can also be text directly in the formula.
• [start_num] (optional) is where you want the search to begin within the within_text. If you leave this out, it’ll start from the first character.

Keep in mind, the SEARCH function doesn’t care about capitalization. If you need a case-sensitive search, the FIND function is what you’re after. Also, if there are multiple matches, SEARCH only returns the position of the very first one it finds.

The SEARCH function also supports wildcard characters:

• Question mark (?) is a stand-in for any single character.
• Asterisk (*) will match any series of characters.
• Tilde (~) lets you search for a literal question mark or asterisk. Type a tilde (~) before the wildcard.

Searching from the Beginning of a Text String

You can easily search for a character or word from the start of a string, it will give you the position. It goes from the first character, left to right.

Here’s how the formula looks:

=SEARCH("Angeles",A2)

or to be more specific about starting at the beginning, use this:

=SEARCH("Angeles",A2,1)

This formula is looking for “Angeles” inside the text “Los Angeles” (which is in cell A2) and returns the number 5.

You can also use a cell reference for the search term, like this:

=SEARCH(D3,A11,1)

Case-Insensitive Search

As I mentioned earlier, the SEARCH function doesn’t care about case. If you search for ‘SaLt’, ‘SALT’, or ‘salt’, the result is the same.

Searching from a Specific Starting Position

The SEARCH function lets you start searching from a specific point, not just the beginning. To do this you need to use the optional start_num argument in the formula.

For example, to look for “Sparta” starting from the 9th character:

=SEARCH(C2,A2,9)

This formula searches for “Sparta” (in cell C2) within the text of cell A2, beginning at position 9, and returns 14.

Remember, spaces count when you’re counting positions.

Handling Multiple Occurrences

Let’s say you’re looking for “God” and it appears more than once. The SEARCH function will only return the position of the first match after the starting number.

For example, searching for “God” in cell A3 starting at position 2:

=SEARCH(C2,A3,2)

This will return 4, which is the position of the first “God” after the second character.

Now, let’s change the starting point to 5:

=SEARCH(C2,A3,5)

Now the output will be 9, because it ignores the first occurrence of “God”.

When you get a #VALUE! error

You’ll get the #VALUE! error in two cases:

• If the text you’re searching for isn’t in the cell.
• If the starting number is less than zero or more than the length of the text you’re searching in.

For instance, here the starting point is 10, but “God” appears before the 10th character, hence #VALUE! error.

And in this example, the starting position is beyond the length of the text, resulting in the #VALUE! error:

The SEARCH function isn’t just for letters, you can use it to find symbols too. Here, we’ve found the position of the Trademark (™) symbol at position 20.

Using Wildcard Characters

The SEARCH function supports wildcards: question mark (?), asterisk (*), and tilde (~). The question mark matches any single character, the asterisk any number of characters, and tilde matches the literal wildcards.

Example 1: Using Asterisk (*)

Here, we’re using the asterisk to search for “Pencils” using the pattern “Pen*s”:

=SEARCH(D9,A13,1)

The formula finds the text “Pencils” and returns the position number 12, because it matches with the pattern of the search term.

Example 2: Using Wildcard and Start Number

Specifying the starting number in the search formula is very important:

=SEARCH(C2,A10,1)

Here we wanted to find the position of “char” in “character” but instead it found “car” in “Wildcard” because we didn’t specify the start number properly.

To fix it, use the correct starting number:

=SEARCH(C2,A10,8)

Now it correctly finds “char” in “character”.

Example 3: Asterisk before the Search Term

Another example with the wildcard (*):

Example 4: Using Question Mark (?)

The question mark (?) can match a single character. Let’s use it to find “PC940”:

=SEARCH("?C94?",A12,1)

This formula correctly returns the position as 7

Combining SEARCH with Other Functions

The SEARCH function is more powerful when used with other functions like MID, LEFT, RIGHT, or ISNUMBER.

Extracting Substrings Using SEARCH

You can use SEARCH with LEFT, RIGHT, or MID to pull out parts of a string based on the position of a certain character or text. This is very useful for breaking down full names into first, middle, and last names.

Extracting Before Specific Characters

You can use SEARCH with the LEFT function to extract a substring before a specified character:

The syntax for the LEFT function:

=LEFT(text,[num_chars])

• text is the text string or cell that contains the characters you want to extract.
• num_chars is the number of characters you want to extract from the left side.

Here is the general formula to extract text before a specific character:

=LEFT(text,SEARCH("char",text)-1)

• text is the text string or cell reference from which you want to extract text.
• char is the character which denotes where you want to stop the extraction.

If you have a list of full names and you want to extract only the first names:

Use this formula:

=LEFT(A2,SEARCH(" ",A2)-1)

This formula locates the space character’s position (7) then subtracts 1 to exclude the space and uses this number for extracting leftmost 6 characters.

Now, you can auto-fill down the column to extract all first names:

Extracting After Specific Characters

To extract a substring after a character, use a combination of RIGHT, SEARCH, and LEN functions:

The general formula is:

=RIGHT(text,LEN(text)-SEARCH("char",text))

To extract last names from a list of full names:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

Here, the SEARCH function finds the space position. Then LEN finds the total number of characters and subtracts the space position from it to get the length of the last name. Finally, the RIGHT function extracts those characters from the end of the string.

Auto-fill this formula for the rest of the cells to extract all the last names:

Extracting Between Two Characters

To grab a substring in the middle, use the MID function along with SEARCH.

The MID function syntax:

=MID(text,start_num,num_chars)

• text is the text string or cell reference from which you want to extract text.
• start_num is the character position where you want to start the extraction.
• num_chars is the number of characters you want to extract.

Here is the general formula to extract text between two characters:

=MID(text,SEARCH("char",text)+1,SEARCH("char",text)-SEARCH("char",text)-1)

Let’s look at a simple example of the MID function:

=MID(A2,10,4)

This formula starts from the 10th position in the text string of cell A2 and extracts the next 4 characters.

To extract a substring between two characters using MID and SEARCH:

If you have a list of product IDs, and you need to extract the numbers between the ‘-’ and ‘/’ characters.

Use this formula:

=MID(A2,SEARCH("-",A2)+1,SEARCH("/",A2)-SEARCH("-",A2)-1)

This will give you the middle portion of the product IDs.

Now, you can auto-fill down the column to extract the middle portion of the ID from the rest of the rows.

If you want to extract middle names from a list of full names, use this formula:

=MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)-1)

This formula considers the first space to identify the start position and the second space to identify the end of the middle name.

Finding the Nth Occurrence of a Character

If you need to find the position of a character that shows up multiple times, SEARCH by itself will only return the first one. When combined with the SUBSTITUTE function, you can find the second, third, or any nth occurrence easily.

The generic formula:

=SEARCH(CHAR,SUBSTITUTE(text, character, CHAR, [instance_num]))

• CHAR is a function that returns a symbol based on its ASCII code. Use a symbol that doesn’t appear in your text.
• text is where you are searching.
• character is what you’re looking for.
• [instance_num] specifies which occurrence you want to find.

Method 1: Using SUBSTITUTE to Find Nth Occurrence

To find the 2nd occurrence of the character ‘/’ use this:

=SEARCH("$",SUBSTITUTE(A1,"/","$",2))

The SUBSTITUTE function replaces the second instance of / with $, then SEARCH returns the position of $.

You can also use the CHAR function with ASCII code to substitute the search character.

Method 2: Using Nested SEARCH

Here’s another way using nested SEARCH function:

=SEARCH("e",A13,SEARCH("e",A13)+2)

This formula returns the position of the 3rd “e” by adding 2 to the position of the first “e” when searching for the second “e”.

Checking for Text with ISNUMBER and SEARCH

The SEARCH function returns the position of a substring, or a #VALUE! error if it’s not found. The ISNUMBER function returns TRUE for numbers and FALSE for everything else. Combine these two and you can check if a cell contains a specific text.

The generic formula:

=ISNUMBER(SEARCH(substring,text))

• substring is the text you’re searching for.
• text is where you are searching.

For instance, to check if the word “Cow” appears in column A:

=ISNUMBER(SEARCH("Cow",A2))

This returns TRUE if “Cow” is present and FALSE if not.

You can also use a cell reference as the search term:

=ISNUMBER(SEARCH(B$1,A2))

Using IF, ISNUMBER, and SEARCH

If you want a specific output rather than TRUE or FALSE, add the IF function:

For example, to return “Found” if a cell contains “Cow”, and “Not Found” otherwise:

=IF(ISNUMBER(SEARCH(B$1,A2)), "Found","Not Found")

Now you will get “Found” if the text exists and “Not Found” if it doesn’t.