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.
Syntax
=SEARCHB(find_text, within_text, start_num)
Arguments
- find_text (required): The string or character you want to find its position in within_text.
- within_text (required): The string contains the find_text you want to find.
- start_num (optional): The position in the within_text at which to start the search of find_text. If omitting this argument, it is assumed to be 1.
Return Value
The SEARCHB function returns a numeric value.
Function Notes
- 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.
=SEARCHB(B6,C6)
Instead of cell references, you can type the actual find_text and within_text values in the formula as shown below.
=SEARCHB("什么","你在干什么")
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.
=SEARCHB(B6,C6,D6)
Instead of cell references, you can type the actual find_text, within_text and start_num values in the formula as shown below.
=SEARCHB("什么","你在干什么",3)
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.
Related functions
In Excel, the SEARCH function can help you to find the position of a specific character or substring from the given text string as following screenshot shown. This tutorial, I will introduce how to use this SEARCH function in Excel.
The FINDB function finds a text string (case sensitive) within another string, and returns the number of the starting position of the first string inside the second one based on the number of bytes you specify. FINDB 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 REPLACEB function replaces part of a text string with a new text string based on the number of bytes you specify. REPLACEB 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 LEFTB function returns the specified number of characters from the beginning (left) of a supplied string based on the number of bytes you specify. LEFTB 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 RIGHTB function returns the specified number of characters from the end (right) of a supplied string based on the number of bytes you specify. RIGHTB 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 MIDB function returns a specific number of characters from a text string, starting at the position you specify, based on the number of bytes you specify. MIDB 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 Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
