How to Use Excel Search Function

Learn to use using the SEARCH function to determine the location of a text character or string and extracting a particular substring in Excel.

Search function is a text search feature that returns the position of a Search function is an text function that looks for the sub-string or character within a text or string and determines its position. It finds a word within another word , and returns its location within the cell in an integer.

For example, if you employ for instance, if you use the SEARCH function to determine the location of the letter’s or the word’son in the phrase “Jackson”, it will return 5. The return will be only the beginning location of your search within the cell you searched.

It is important to note that the SEARCH function is seldom employed on its own, but it is frequently used in conjunction with FIND, MID LEFT, ISNUMBER as well as other functions. This article we’ll describe how to use the SEARCH function does and how you can use it and how to utilize the SEARCH function in conjunction with other functions within Excel.

Excel SEARCH Function

The SEARCH function searches for characters or text in a text string. It returns the beginning location of the string within the cell specified or the string that is specified.

The syntax for SEARCH Function in Excel Syntax of SEARCH Function in Excel

=SEARCH (find_text, within_text, [start_num])

• Find_text (required) refers to the string or character you are looking for.
• inside_text (required) refers to the location where you search for the find_text. It’s usually the cell reference that has the text string we want to find. But, you could also enter the text straight into formula.
• [start_num[start_num (optional) indicates the location in the within_text string where you would like the search to start. If you do not specify it the start_num parameter, the search will begin with the first character in text within the string.

It is important to note that the SEARCH feature isn’t dependent on the case. If you’re looking for a match that is case-sensitive then you should use the FIND feature. If the FIND function is able to find multiple matches for the search term the function will only show the location of the first word.

Search Function also supports the following wildcard operators:

• The question mark (? ) can be used for matching a single letter or character with the string of text.
• Asterisk (*) is used to match any character using the string.
• Tilde (~) is used to match an actual question mark or an asterisk. Enter the letter tilde (~) prior to the other two wildcards to locate them.

Find a Character or Text in a Text String starting from the beginning

You can utilize to use the SEARCH function to search for the exact character or word in a text string starting from the beginning and return to its location. It scans the string starting with the first character from left to right, until at the very end within the cell.

To find a particular word or character in the text string, use this formula:

=SEARCH("Angeles",A2)

or

To begin searching at to the start of the string, you can choose to either leave out one of the arguments (start_num) or change it to 1:

=SEARCH("Angeles",A2,1)

The formula above searches for the word “Angeles” starting from the first character of the string “Los Angeles’ (A2) and returns the location of the beginning position of the text that was searched (Angeles) 5.

You may also make use of cell references to the argument find_text within the following formula

=SEARCH(D3,A11,1)

Case-Insensitive Search

As we have mentioned previously that the SEARCH function isn’t case-sensitive, so you can search for “SaLt”, “SALT” or ‘Salt’ with the formula, and you will get the exact result regardless of the situation the within_text or find_text arguments use.

Search for a Character(s) or Text in a Text Stringfrom Specific Starting Position

It is possible to use the SEARCH function can be used to determine the location of the word or text within strings by starting with a particular location within the text rather than the initial character. By putting the start number (start_num) within the formula you are able to specify the location from which you would like to begin the search.

Example 1:

To find words from a particular location, use the following formula:

=SEARCH(C2,A2,9)

In this case, we have specified that the final argument (start_num) to be 9. Therefore, the SEARCH function begins looking at the term Sparta (C2) starting at the position 9. (from that space-character following”Ghost” Ghost) within the cell A2 string and returns the location of the search term as 14.

When counting positions it is important to include the character space also counted.

If there is Multiple Occurrences of the Searched Text

Let’s look at another scenario. If we are trying to determine the location of the word “God” We can use the following formula:

=SEARCH(C2,A3,2)

The number that starts the search (start_num) is 2 in this case and the search begins with the ‘y’ symbol. While the string contains two different instances of the word God within the text, it will only return the position of the initial word starting at the start. Therefore the output is 4.

Let’s change the starting time to 5.

=SEARCH(C2,A3,5)

You will now see the result as 9 because the search starts with the letter “o” (in the very first use of God) The first instance of the word ‘God’ is not considered.

#VALUE! error

There are two scenarios in which you’ll encounter the error ‘#VALUE!’ when you search for the text using the SEARCH function:

• If the search term (find_text) is not present within the text string specified.
• If the number that starts (start_num) is lower than zero, or is greater than that of text within.

As you can see in the below the starting point for the search is 10. The full word “God” are in 10th position, which is why there’s a #VALUE! error.

In the example below the beginning number is greater than what is in the text within and we receive the #VALUE! error.

The SEARCH function is able to locate any type of characters, even symbols. For instance, we searched for the position for the Trademark ((tm)) symbol in the string we were given and discovered its position to be 20.

Search using Wildcard characters in Excel SEARCH function

The SEARCH function can be used to search for wildcards, such as questions marks (? ) or an asterisk (*), and tilde (~). A Question mark (?) is a single character, while the Asterisk (*) refers to any sequence of characters and the Tilde (*) is a match for any of the two other wildcards.

Example 1:

In the following example the example below, a in the following example, a wildcard (*) is used to determine the location of the word “Pencils” (Pen*S) within the cell A13 string:

=SEARCH(D9,A13,1)

The search term is “Pen*s” (D9) Therefore, the formula searches for any text that begins with ‘Pen’ , and then ends with’s’, and contains any number of characters between in A13. It is a match for the word ‘Pencils’ in the text and then returns the location as 12.

Example 2:

If you are defining a the starting number (start_num) it is necessary to enter the correct number of start to start the search.

For instance, the following formula will determine the location of the word “c*r” in the cell’s string A10:

=SEARCH(C2,A10,1)

We were trying to determine the location of the word ‘char’ within the word “character” but however, we found the position of the word ‘car’ in the word ‘Wildcard’. This is five.

To correct this issue to fix this, we can alter the number of characters that are used for searching to eight. The function will now begin the search with the character “d” located at the very end of the wildcard. It then determines the position of the character ‘Char’ within “Characters”.

Example 3:

Another instance where the wildcard is used prior to the search term. No regardless of how many characters are present before the word ‘war’ within cell A3, the system will show the position of the search as 1.

Example 4:

In the following example In the below example, the in the following example, question (?) is used to determine the location of the text “PC940” (?C94?) within the string in cell A12:

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

What ? denotes any single character, therefore the formula is a match to the string “PC940” that is at position 7.

Utilizing the SEARCH Function in conjunction with Other Functions

It is the SEARCH function is typically used in conjunction with other functions like LEFT, MID, or ISNUMBER to make complex calculations.

Find and extract Strings using SEARCH Function

It is possible to use the SEARCH function using LEFT or RIGHT or MID functions to find and locate a substring within text strings to either the left or right of a particular character, or in between two characters. For example, these kinds of combinations can be useful for breaking down a list of full names in first and last names, as well as middle names.

Find a Substring Prior to a Certain Character(s) by using the SEARCH and LEFT functions

If you own an unstructured text string or a string list that you want to search, use the search function along using the LEFT feature to move the substring or a portion of the string into an additional column.

The LEFT function allows you to get a certain amount of characters out from the left-hand side of the string, beginning with the first character in the string.

Function of the Syntax LEFT:

=LEFT(text,[num_chars])

There are two arguments to support it:

• text is the string of text that refers to the cell which contains the characters you wish to extract.
• number_chars is the number of characters that you want to take in the order left-to-right within the provided document (this includes the space between the characters).

If you have a list of complete names in a column , and you wish to separate the first names in separate columns. You can accomplish this using LEN as well as SEARCH. This is how:

The syntax used to extract the substring prior to a particular character:

Utilize the formula below (a mixture that combines LEN AND FIND) to find the substring prior to a particular character:

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

• text defines the text string from which we wish to extract a substring or text. This could refer to a string of text or an identifier to a cell which contains the string.
• Char is the particular character we are looking for to establish the position.

The SEARCH function determines the position of the specific character within the text string. Then one is subtract from the location the number (position of the specified characters) in order to calculate the size of the string that is to be extracted. The length is then supplied to the LEFT in order to extract the characters from left to right within the string.

Example:

Use the formula below to separate one name out of the complete name:

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

The formula above uses the SEARCH function to determine the location (which equals seven) that the character space (” “) in between the first and the last name. It then subtracts 1 to eliminate the space itself. Then we get an estimate of length for the name that is the longest (leftmost six characters). The number is then passed by the function LEFT in order to give the characters (6) from left to right in the string.

If the list of strings is arranged in a way that they all contain space characters between. You can then use the Excel FIND and LEFT formulas to remove strings from a column by using auto-fill, without having to provide the character arguments for every formula.

To do this, choose the cell with the formula (B2) then drag the handle for fill (little green square on lower left of cell you have selected) down to other cells in order to apply the formula to the column. The first names of the participants are inserted in column B, as shown below:

Find a Substring after a A Specific Character(s) by using SEARCH and Right

If you are looking the substring to be extracted following the specific character, you must make use of the right, search, as well as LEN function. It is the SEARCH as well as the LEN function are nested within the num_char argument of right function to determine the characters to extract, and the right function will extract the characters.

A syntax used for extracting a substring following the symbol:

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

If we have a column with all names, and you wish to separate the last names into distinct columns, you can accomplish this using this formula

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

In the formula above in the above formula, it is evident that the SEARCH function will return the location of the space character “‘,” which is 7 and the LEN function determines that the number of characters contained in the string (string length). The location of the space character will be subtracted from total number characters within the string to determine how long the final name. This is then passed by the right function in order to remove the characters that are at to the ending of the string.

After the last name has been extracted into a separate column Drag the fill handle into other cells in order to apply the method to column. The last names are extracted to column C, as illustrated below:

Find a Substring between Two Characters by using the MID and SEARCH Functions

If you want to remove the middle part from the string of text, you can do this by using the MID and SEARCH functions.

Excel MID Function

It is the MID Excel Function is used to extract the middle part of the string. In order to do this it needs an unstructured text string the beginning point (position) as well as the number of characters to be extracted.

Syntax:

=MID(text,start_num,num_chars)

• text defines the text string from which we wish to extract a substring or text. This could refer to a string of text or an identifier to a cell which contains the string.
• start_num indicates the location within the text string where you would like to begin the extraction. If you do not specify it the start_num, the search will begin at the beginning of the text string within.
• number_chars is the number of characters you want to take in the order left-to-right within the provided document.

The arguments for start_num and num_chars can be passed through the SEARCH function of the MID function in order to find the substring that is that is comprised of two characters.

Let’s see what the MID function operates using this easy example:

=MID(A2,10,4)

The formula count the characters in A2 left-to-right, until it gets until the 10th character, and then returns the following seven characters (including the 10th character).

To create a substring in that is comprised of two letters using SEARCH and MID, you can use the syntax below:

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

• text : A string from which we want to take a substring, it could be a text string , or an identifier for an element that contains an text.
• character A particular character that we need to establish the location.

Example 1:

Imagine you have a column of Product IDs. You want to find ID numbers that are between the ‘-‘ and”/’ characters.

It is necessary to determine the positions of the two specified characters Then subtract the location of the second specified characters from that of the first character, then subtract 1 of the results to figure out the number of characters we need to take with the position of the first character.

To find the middle string from two characters, type this formula in the blank cell:

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

Explanation:

• A2 is the cell that holds the text string that was originally used from which we’re trying to take the substring.
• Search ("-",A2)+1 (start_num) is the location of the first identified characters (+) and then adds 1 to begin the extraction process from to the following character (start_num=5).
• SEARCH("/",A2)-SEARCH("-",A2)-1 (num_chars) finds the length of the substring we want to extract between ‘-‘ and ‘/’ characters by subtracting the position of ‘/’ (13) from the position of ‘-‘ (4) and subtracting 1 from the result. This tells the MID function the number of characters to take out.

Now it is clear that the MID function is aware of the number of characters it needs to take from the string and also where to begin. It returns the middle string from cell C2.

Then, you’ll be able to apply the formula to the remaining cells to get the middle string of the entire column.

Example 2:

The SEARCH and MID are helpful when extracting middle-names from the list full names. To find the middle name, follow this formula:

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

The issue is that both mentioned characters have space character (same) and the formula will not be able to discern the two this could result with #VALUE!. To solve this issue problem, we nested the SEARCH function within another SEARCH function, and then included 1 in the argument ‘num_chars’ to determine the location of the second character.

• SEARCH(" ",A2)+1 determines the point of entry in the extraction process by adding one to the location of the first space character.
• Search(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)-1 subtracts the location of the second space by subtracting the location of the first space, and then subtracts 1 from the result to eliminate the trailing space. The result will tell the formula the number of characters to be extracted.

With the text string, the starting number, as well as the number of characters the MID function separates an intermediate name (A2) from its complete names (A2).

Find the Nth Occurrence, or the position of a character in the String by using the SEARCH function

If you’re trying to locate the location of a text or character that is repeated in the same string the search function will only return the first match for the character or text. When you combine both the SEARCH as well as the SUBSTITUTE functions, you will be able to locate the nth instance or the position (such as the third or second instance) of a particular text (or strings or string of characters) in the text string quickly.

Here’s the formula that can be used to determine the location of the Nth time the character:

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

Where

• Char is an operation that gives a symbol in response to it’s ASCII code. You can choose the code of any ASCII code or the symbol you’re certain won’t be found within the text.
• text is the string of text that is a reference to the cell which includes the string from which you are trying to locate the nth time.
• Character can be described as the one you’ll need to locate the nth instance.
• [instance_num[instance_num indicates the instances in that character(s) (or text) you want to locate.

Example 1:

In the following example, we are trying to determine the 2nd instance of the character “/” using this formula

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

It is the SUBSTITUTE function actually looks for characters or texts and substitutes it for another. In the formula above we’re replacing for the letter (/) with a character ‘$’, that character, which will not appear within the text. Therefore, that when our SUBSTITUTE functions replaces 2 ^2ndoccurrence of ‘”/” in the string with an ‘$’ symbol, ‘$’, it returns the location of the character.

It is also possible to use CHAR ASCII code within the CHAR argument of the formula to substitute search characters (or) to achieve the same outcomes.

Example 2:

Another method that will help you determine the nth time you’ve seen one particular character in text strings.

The formula below will calculate the location of the 3rd appearance in the word “e” in cell A1:

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

The formula above will return the 3rd ‘e in the form 23. You can define the location of the expression by altering the final argument (2) in the same manner. If you are trying to locate the first instance of “e”, change 2 to zero. To find the second instance of “e”, change 2 to 1, and so on.

Combine ISNUMBER and SEARCHfunction to Search for the Text of a Specific Word

As we all know, the SEARCH function looks for a particular substring within the string and returns its numerical location when the text is found, otherwise it returns an #VALUE! error. The ISNUMBER function determines whether a cell has numbers or not. If it is it returns TRUE in the other case, it returns FALSE.

But, when you use the ISNUMBER function is combined together with SEARCH, it will determine whether a cell has particular text returning TRUE in the event that the text is found, if not, the cell returns FALSE.

Here’s the formula that can be used to determine if an individual cell has the text:

=ISNUMBER(SEARCH(substring,text))

Where

• substring is the text string that you are trying to locate.
• text is the string or cell where you’re trying to find the substring.

Example:

In the following dataset We want to determine whether or not the term Cow is present within column A.

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

Then, you must enter the formula in the cell A2 and then auto-fill the formula into the column. The ISNUMBER function determines if the string ‘Cow’ is present in the cell A2 and returns FALSE.

The SEARCH function examines A2’s cell for the word “Cow” and returns the “#VALUE!” failure (because there is no text Cow isn’t present). The number ‘#VALUE!’ is not a number , so it is not a number, so the ISNUMBER function returns “FALSE”. In cell B2 In cell B2, the SEARCH function searches for the word ‘Cow’ , and give its position number and the ISNUMBER returns TRUE..

You can also make use of references to cells to input a string into the formula:

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

Find Specific Text using the IF, ISNUMBER, as well as SEARCH functions

If you’d like to find something different from TRUE or FALSE when a substring is discovered within a string, you can utilize the IF function using the ISNUMBER function and also with SEARCH.

For instance, we’ll attempt to determine if every row in Column A has the word “Cow” in order to Return “Found” if a cell has that text and “Not Found” if not:

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

You can copy the formula from B2 into the remaining cells by using the fill handle. In the formula it is the SEARCH function compares the B1 value against A1’s cell and results in an error. Therefore, the ISNUMBER function returns a false. In the end, the If function returns ‘Not Found’.

That’s it.