Excel FINDB Function
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.
Syntax
=FINDB(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 find of find_text. If omitting this argument, it is assumed to be 1.
Return Value
The FINDB function returns a numeric value.
Function Notes
- Spaces count as characters.
- The FINDB function is case sensitive. To do a case-insensitive search, you can use SEARCHB instead.
- FINDB does not allow wildcard characters in searches. To use wildcard characters in searches, please use SEARCHB function.
- FINDB 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, FINDB counts each character as 1 just like FIND.
- If find_text is empty (“”), the FINDB function returns the character numbered start_num. If start_num is omitted, returns 1.
- FINDB counts actual values instead of the look of your data. For example, A1 is date 9/1/2022, FINDB(0,A1) returns 4, as the actual value in A1 is 44805 which was just formatted as a date value.
- FINDB 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-sensitive search from the beginning
Take the table below as an example. To find the starting position of find_text (case sensitive) 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.
=FINDB(B6,C6)
Instead of cell references, you can type the actual find_text and within_text values in the formula as shown below.
=FINDB("什么","你在干什么")
Example of case-sensitive 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.
=FINDB(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.
=FINDB("什么","你在干什么",3)
FINDB VS. FIND
FINDB counts each double-byte character as 2 and each single-byte character as 1. While FIND counts both double- and single-byte character as 1. You can compare the results returned by FINDB and FIND as shown below.
FINDB VS. SEARCHB
Both FINDB and SEARCHB 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 FINDB and SEARCHB as shown below.
Related functions
The FIND function is used to find a string within another string, and returns the starting position of the string inside another one. Supposing, =FIND("ea","pear") returns 2 which means to find the starting position of "ea" in "pear".
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 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.
