A to Z of Excel Functions: The SEARCHB function.

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the SEARCHB function.

The SEARCHB function

The SEARCHB function locates one text string within a second text string and returns the number in respect of the starting position of the first text string from the first character of the second text string.  When a double byte character set (DBCS) is set as the default language, SEARCHB counts two (2) bytes per character, rather than the usual one (1).

It employs the following syntax to operate:

The SEARCHB function has the following arguments:

• find_text: this is required and represents the text you wish to find
• within_text: this is also required.  This is the text in which you want to search for the value of the find_text argument
• start_number: this argument is optional and denotes the character number in the within_text argument at which you want to start searching.

It should be further noted that:

• as stated above, the SEARCHB function is not case sensitive.  If you want to create a case sensitive search, use FINDB
• you may use wildcard characters, such as the question mark (?) and asterisk (*), in the find_text argument:
  • a question mark matches any single character
  • an asterisk matches any sequence of characters
  • if you want to find an actual question mark or asterisk, type a tilde (~) before the character
• if the value of find_text is not found, the #VALUE! error value is returned
• if the start_number argument is omitted, it is assumed to be 1
• if start_number is not greater than zero (0) or is greater than the length of the within_text argument, the #VALUE! error value is returned
• use start_number to skip a specified number of characters.  The SEARCHB function always returns the number of characters from the start of the within_text argument, counting the characters you skip if the start_number argument is greater than one (1)
• this function may not be available in all languages.  SEARCHB counts two (2) bytes per character only when a DBCS language is set as the default language (the languages that support DBCS include Japanese, Chinese (Simplified), Chinese (Traditional), and Korean
• otherwise, SEARCHB behaves the same as SEARCH, counting one (1) byte per character.

For example,

equals 3 because each character is counted by its bytes.  The first character has two bytes, so the second character begins at byte 3 if the default language uses DBCS.  If not, it behaves like SEARCH:

The SEARCH formula equals 2 since the search item is in the second position within the string.  SEARCH returns 2 regardless of the default language setting on your computer.

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every business day.