SEARCHB Excel Function

SEARCHB Function in Excel: Finding Substrings in Double-Byte Character Sets

The SEARCHB function in Excel is a powerful tool for locating substrings within text strings, particularly useful when working with double-byte character set (DBCS) languages such as Japanese, Chinese, and Korean. This function counts each double-byte character as 2, making it essential for accurate text parsing in these languages.

Syntax and Parameters

The syntax for the SEARCHB function is:

SEARCHB(find_text, within_text, [start_num])

• find_text: The substring you want to locate.
• within_text: The main text string to search within.
• start_num: (Optional) The starting position for the search. If omitted, the search begins at the first character.

Key Features

• Case-insensitive searching
• Support for wildcard characters (? and *)
• Accurate handling of double-byte characters

Practical Applications

The SEARCHB function can be used for various tasks, including:

• Text parsing and data extraction
• Validating the presence of specific substrings
• Conditional formatting based on text content
• Extracting information when combined with other text functions

Examples

Basic usage:

=SEARCHB("B", "ABCDE")

This returns 2, as “B” is the second character in the string.

Working with double-byte characters:

=SEARCHB("漢字", "英語漢字テキスト")

This locates the position of “漢字” within the Japanese text string.

Potential Issues and Considerations

• The function is not case-sensitive. Use FINDB for case-sensitive searches.
• Mixing single-byte and double-byte characters can lead to unexpected results if not carefully managed.
• Returns a #VALUE! error if the substring is not found.

Compatibility

The SEARCHB function is supported in:

• Excel 2016
• Excel 2019
• Excel for Microsoft 365

It’s particularly relevant for users working with DBCS languages in these Excel versions.

Conclusion

The SEARCHB function is an invaluable tool for Excel users working with languages that use double-byte character sets. Its ability to accurately locate substrings while accounting for the unique characteristics of these languages makes it essential for text manipulation, data validation, and information extraction tasks in multilingual environments.