Note: The other languages of the website are Google-translated. Back to English

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.

searchb function 1


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)

searchb function 2

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)

searchb function 3

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 function 4


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.

searchb function 5


Related functions

Excel SEARCH Function

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.

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.

Excel REPLACEB Function

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.

Excel LEFTB Function

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.

Excel RIGHTB Function

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.

Excel MIDB Function

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

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

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, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

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, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL