How to use the SEARCH and SEARCHB Functions?

anonymous person with binoculars looking through stacked books
  • Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

This post will show you how to use the SEARCH and SEARCHB functions in Microsoft Excel. We will will cover their descriptions, their respective formula syntaxes, key conditions of their use, and also examples which apply their use.

A labyrinth - symbolises SEARCH and SEARCHB Functions.
Image by PIRO4D from Pixabay.

Description of the SEARCH and SEARCHB functions

The SEARCH and SEARCHB functions work by tracking down one text string inside a second text string. They then yield the number of the beginning position of the first text string from the initial character of the second text string. For instance, to locate the position of the letter “s” in the word “focus”, you can use the following function:

=SEARCH(“s”,”focus”)

This function returns 5 because “s” is the fifth character in the word “focus.”

In addition, you can even search for words among other words. For example, the function

=SEARCH(“base”,”database”)

returns 5, since the word “base” starts at the fifth character of the word “database”. You can use the SEARCH and SEARCHB functions to figure out the position of a character or text string inside another text string, and then employ the MID and MIDB functions to return the text, or use the REPLACE and REPLACEB functions to alter the text. These functions are exemplified in Example 1 in this post.

Important: 

  • These functions might only be accessible in some languages.
  • SEARCHB counts 2 bytes per character in the event when a DBCS language is configured as the automatic language. Conversely, SEARCHB acts precisely like SEARCH, counting 1 byte per character.

The languages that promote DBCS include Japanese, Chinese (Simplified), Chinese (Traditional), and Korean.

A telescope - represents SEARCH and SEARCHB functions.
Image by Hans Braxmeier from Pixabay.

Syntax of the SEARCH and SEARCHB functions

SEARCH(find_text,within_text,[start_num])

SEARCHB(find_text,within_text,[start_num])

The SEARCH and SEARCHB functions contain these specific arguments:

  • find_text  –  Required. The text that you are looking to find.
  • within_text  –  Required. The text in which you seek to search for the value of the find_text argument.
  • start_num  –  Optional. The character number in the within_text argument at which you prefer to begin searching.

Remark of the SEARCH and SEARCHB functions

  • The SEARCH and SEARCHB functions are not case-sensitive. If you are specifically seeking to do a case-sensitive search, you can use FIND and FINDB.
  • You can deploy the wildcard characters — the question mark (?) and asterisk (*) — in the find_text argument. A question mark corresponds to any single character; an asterisk matches any sequence of characters. If you want to capture an actual question mark or asterisk, enter a tilde (~) before the character.
  • If the value of find_text is still undiscovered, the #VALUE! error value is collected.
  • If the start_num argument is skipped, it is automatically taken as 1.
  • If start_num is lesser than 0 (zero) or exceeds the length of the within_text argument, the #VALUE! error value is yielded.
  • Use start_num to omit a confirmed number of characters. Applying the SEARCH function as an example, let’s say that you are working with the text string “AYF0093.YoungMensApparel”. To locate the position of the first “Y” in the descriptive part of the text string, configure start_num equal to 8 so that the serial number portion of the text (in this case, “AYF0093”) is exempt from the search. The SEARCH function begins the search operation at the eighth character position, captures the character that is elaborated in the find_text argument at the next position, and brings back the number 9. The SEARCH function consistently gathers the amount of characters from the outset of the within_text argument, counting the characters you ignore if the start_num argument is higher than 1.

Examples

Copy the sample data in the below table, and paste it into cell A1 of a brand new Excel worksheet. For formulas to display results, choose them, press F2, and then press Enter. If you have to at any time, you can modify the column widths in order to view the entire data in all its glory!

Data
Statements
Profit Margin
margin
The “boss” is here.
FormulaDescriptionResult
=SEARCH(“e”,A2,6)Position of the first “e” in the string in cell A2, commencing at the sixth position.7
=SEARCH(A4,A3)Position of “margin” (string for which to search is cell A4) in “Profit Margin” (cell in which to search is A3).8
=REPLACE(A3,SEARCH(A4,A3),6,”Amount”)Replaces “Margin” with “Amount” by first searching for the position of “Margin” in cell A3, and then overwriting that character and the next five characters with the string “Amount.”Profit Amount
=MID(A3,SEARCH(” “,A3)+1,4)Returns the first four characters that come after the first space character in “Profit Margin” (cell A3).Marg
=SEARCH(“”””,A5)Position of the first double quotation mark (“) in cell A5.5
=MID(A5,SEARCH(“”””,A5)+1,SEARCH(“”””,A5,SEARCH(“”””,A5)+1)-SEARCH(“”””,A5)-1)Returns only the text contained within the double quotation marks in cell A5.boss

Similar Topics to Explore

TEXT Function

LEFT, LEFTB

RIGHT, RIGHTB

TRIM Function

CONCATENATE

CONCAT

FIND, FINDB

Other Links

Excel Tutorial

Expand your Office skills

EXPLORE TRAINING

Got It

Get instant Excel help

CONNECT TO AN EXPERT NOW

Subject to Got It terms and conditions.

Leave a Reply

%d bloggers like this: