Excel SEARCHB Function
The SEARCHB function finds a text string (case-insensitive) within another string, and returns the number of the starting position of the first string inside the other one based on the number of bytes you specify. SEARCHB is intended for use with languages that use the double-byte character set (DBCS), such as Chinese (Simplified), Chinese (Traditional), Korean, and Japanese. The function counts each double-byte character as 2.
The SEARCHB function returns a numeric value.
- Spaces count as characters.
- The SEARCHB function is case-insensitive. To do a case-sensitive search, you can use FINDB instead.
- SEARCHB allows wildcard characters in searches. For example, you want to find the position of the string that starts with t and ends with l, you can supply t*l as find_text.
- SEARCHB always counts each double-byte character as 2 when you have enabled the editing of a language that supports DBCS and then set it as the default language. Otherwise, SEARCHB counts each character as 1 just like SEARCH.
- If find_text is empty (“”), the SEARCHB function returns the character numbered start_num. If start_num is omitted, returns 1.
- SEARCHB counts actual values instead of the look of your data. For example, A1 is date 9/1/2022, SEARCHB(0,A1) returns 4, as the actual value in A1 is 44805 which was just formatted as a date value.
- SEARCHB returns the #VALUE! error if:
- find_text does not appear in within_text;
- start_number is greater than the length of within_text;
- start_number < 1.
Example of case-insensitive search from the beginning
Take the table below as an example. To find the starting position of find_text within within_text, please copy or enter the formula below in the top cell (E6) of the result list, and press Enter to get the result. Then select the result cell, and drag the fill handle (the small square in the lower-right corner of the selected cell) down to apply the formula to the below cells.
Instead of cell references, you can type the actual find_text and within_text values in the formula as shown below.
Example of case-insensitive search from specific position
To search for the find_text in within_text from a specific position based on the number of bytes you specify, and get the beginning position of first found find_text in within_text, you should add the third start_num argument. Enter the formula below in the top cell (F6) of the result list, and press Enter to get the result. Then select the result cell, and drag the fill handle (the small square in the lower-right corner of the selected cell) down to apply the formula to the below cells.
Instead of cell references, you can type the actual find_text, within_text and start_num values in the formula as shown below.
SEARCHB VS. SEARCH
SEARCHB counts each double-byte character as 2 and each single-byte character as 1. While SEARCH counts both double- and single-byte character as 1. You can compare the results returned by SEARCHB and SEARCH as shown below.
SEARCHB VS. FINDB
Both SEARCHB and FINDB functions returns the starting position of the first instance of find_text in within_text in bytes. However, FINDB is case sensitive, while SEARCHB is not. SEARCHB supports using wildcard characters in searches, while FINDB does not. You can compare the results returned by SEARCHB and FINDB as shown below.