Master the SEARCHB Function: Byte-Based Text Search in Excel
=SEARCHB(find_text, within_text, [start_num])The SEARCHB function is a specialized text search tool in Excel that operates on byte-level positioning rather than character positioning. This advanced function proves invaluable when working with multibyte character sets, particularly in Asian languages like Chinese, Japanese, and Korean where each character can consume multiple bytes in memory. Unlike the standard SEARCH function which counts characters, SEARCHB counts bytes, making it essential for precise text manipulation in international datasets. Understanding SEARCHB is crucial for data analysts, international business professionals, and developers working with multilingual content who need exact byte-position information for text extraction and validation tasks. The SEARCHB function returns the starting byte position of one text string within another text string, performing case-insensitive searches by default. This byte-counting capability becomes particularly important when you're processing data from different character encoding systems or when you need to extract substrings based on byte positions rather than character counts. While less commonly used than SEARCH in English-language environments, SEARCHB is indispensable in organizations operating across multiple language markets. Mastering this function alongside related byte-based functions like FINDB and MIDB enables you to build robust, internationally-compatible Excel solutions that handle complex text processing scenarios with precision and reliability.
Syntax & Parameters
The SEARCHB function follows the syntax: =SEARCHB(find_text, within_text, [start_num]). The find_text parameter is required and specifies the text string you want to locate within another string. This parameter accepts direct text values enclosed in quotation marks or cell references containing text. The within_text parameter is also required and represents the text string being searched; it's typically a cell reference or formula result containing the source text. The optional start_num parameter designates the byte position from which to begin searching, allowing you to skip initial bytes and search only within specific portions of text. If omitted, start_num defaults to 1, beginning the search from the first byte. A critical distinction between SEARCHB and its character-based counterpart SEARCH lies in how they measure position. SEARCHB counts bytes rather than characters, which means in multibyte character sets, a single character might equal multiple bytes. For example, in UTF-8 encoding, a Chinese character typically occupies three bytes, so finding its position requires byte-counting logic. The function returns the byte position as a number, or #VALUE! error if the find_text is not found. SEARCHB is case-insensitive by default, treating uppercase and lowercase letters identically. When working with start_num, remember that byte positions don't always align with character boundaries in multibyte systems, requiring careful calculation. This function works exclusively with text and cannot process numeric values without prior text conversion, making data type validation essential before formula implementation.
find_textwithin_textstart_numPractical Examples
Finding Product Code Position in Invoice Description
=SEARCHB("SKU-", "Product SKU-12345 from Japan", 1)This formula searches for the text 'SKU-' within the invoice description starting from byte position 1. Since 'SKU-' contains only ASCII characters (1 byte each), the result will be the byte position where 'SKU-' begins. The function locates the pattern and returns its byte position, enabling subsequent MIDB formulas to extract the complete product code.
Locating Chinese Characters in Multilingual Customer Data
=SEARCHB("北", A2, 1)This formula searches for the Chinese character '北' within cell A2 starting from the first byte. Since Chinese characters in UTF-8 typically occupy 3 bytes each, SEARCHB returns the byte position rather than character position. This result can be used with MIDB to extract address segments based on byte boundaries, ensuring proper handling of multibyte characters that SEARCH would miscount.
Extracting Portion of Text After Byte Position
=SEARCHB("|", "Product Name|Description|Price", 1)This formula locates the first pipe delimiter '|' in the product record. The result provides the byte position of this delimiter, which can be combined with MIDB to extract text segments based on byte offsets. This approach ensures accurate text extraction when dealing with encoded data or systems that require byte-level precision for field parsing.
Key Takeaways
- SEARCHB counts byte positions rather than character positions, making it essential for multibyte character sets like Chinese, Japanese, and Korean
- The function returns the starting byte position of find_text within within_text, or #VALUE! error if not found; it's case-insensitive by default
- Always combine SEARCHB with MIDB and LENB when extracting text, as these functions operate on the same byte-level logic
- Use the optional start_num parameter to search within specific portions of text or to find multiple occurrences sequentially
- Wrap SEARCHB in IFERROR for robust formulas that handle missing text gracefully without cascading errors
Pro Tips
Always use LENB instead of LEN when working with SEARCHB in multibyte environments. LENB counts actual bytes while LEN counts characters, ensuring your byte calculations remain accurate throughout formulas.
Impact : Prevents off-by-one errors and formula failures when extracting or validating text containing multibyte characters. Ensures consistent byte-based logic across all related formulas.
Wrap SEARCHB in IFERROR to handle cases where find_text doesn't exist: =IFERROR(SEARCHB("text", A1), 0). This returns 0 instead of #VALUE! error, making downstream calculations more robust.
Impact : Eliminates cascading errors in complex formulas and provides clean fallback values for further processing. Improves spreadsheet reliability and reduces debugging time.
Test your SEARCHB formulas with actual multibyte character samples from your data source. Different encodings (UTF-8 vs UTF-16) may produce different byte counts for identical characters.
Impact : Catches encoding-related issues early before deploying formulas across large datasets. Ensures formulas work correctly with your specific data source and character encoding system.
Use start_num parameter to search for multiple occurrences sequentially. Nest SEARCHB calls: =SEARCHB("x", A1, SEARCHB("x", A1) + 1) finds the second occurrence of 'x' by starting after the first match.
Impact : Enables finding nth occurrence of text without helper columns. Allows sophisticated text parsing and analysis of repeated patterns within single cells.
Useful Combinations
Extract Text Segment Using SEARCHB and MIDB
=MIDB(A1, SEARCHB("START", A1), SEARCHB("END", A1) - SEARCHB("START", A1))This combination finds both the start and end delimiters' byte positions, then extracts the text between them using MIDB. Perfect for parsing encoded data or extracting specific fields from concatenated strings where byte-level precision is required.
Conditional Text Extraction with SEARCHB and IF
=IF(ISERROR(SEARCHB("keyword", A1)), "Not found", MIDB(A1, SEARCHB("keyword", A1), 10))This formula checks whether the search text exists before attempting extraction. If SEARCHB returns an error (text not found), it displays 'Not found'; otherwise, it extracts 10 bytes starting from the found position. This prevents formula errors and provides user-friendly feedback.
Find Position and Calculate Remaining Bytes with SEARCHB and LENB
=LENB(A1) - SEARCHB("delimiter", A1) + 1This combination calculates how many bytes remain after a delimiter is found. Useful for extracting everything after a specific position: =MIDB(A1, SEARCHB("delimiter", A1), LENB(A1) - SEARCHB("delimiter", A1) + 1) extracts all remaining bytes from the delimiter onward.
Common Errors
Cause: The find_text parameter is not found within within_text, or one of the parameters contains non-text data types like numbers or empty strings without proper conversion.
Solution: Verify that find_text actually exists in within_text using case-insensitive comparison. Convert numeric values to text using TEXT() function. Check for leading/trailing spaces using TRIM(). Example: =SEARCHB("code", TRIM(A1), 1) ensures spaces don't cause false negatives.
Cause: The start_num parameter is invalid—either a negative number, zero, or exceeds the total byte length of within_text, causing the search to start at an impossible position.
Solution: Ensure start_num is a positive integer and doesn't exceed the byte length of within_text. Use LEN() or LENB() to verify text length. Example: =IF(start_num<=LENB(A1), SEARCHB("text", A1, start_num), "Out of range") adds validation before searching.
Cause: The function name is misspelled as SEARCH_B, SEARCHB(), or used in Excel versions predating 2007 where SEARCHB wasn't available, or the formula syntax is incorrect.
Solution: Verify correct spelling: SEARCHB (no underscore or spaces). Confirm your Excel version supports SEARCHB (2007 and later). Check that all parameters are properly enclosed in parentheses and separated by commas. Ensure the formula begins with = sign.
Troubleshooting Checklist
- 1.Verify both find_text and within_text parameters contain actual text data; convert numbers using TEXT() function if necessary
- 2.Confirm start_num parameter is a positive integer between 1 and LENB(within_text); use validation to prevent #NUM! errors
- 3.Check that find_text actually exists in within_text using case-insensitive comparison; remember SEARCHB is not case-sensitive
- 4.Verify your Excel version is 2007 or later; SEARCHB is not available in Excel 2003 and earlier versions
- 5.When working with multibyte characters, confirm your data encoding (UTF-8, UTF-16, etc.) matches your LENB and MIDB calculations
- 6.Use IFERROR wrapper to catch #VALUE! errors and provide meaningful fallback values for robust formula design
Edge Cases
Searching for empty string or null values
Behavior: SEARCHB returns 1 when find_text is empty, as it technically matches at the first byte position. If within_text is empty, SEARCHB returns #VALUE! error.
Solution: Add validation: =IF(OR(find_text="", within_text=""), "Invalid input", SEARCHB(find_text, within_text)) to prevent unexpected results or errors
Empty string matching is rarely useful in practice; always validate input data before searching
start_num parameter exceeds the byte length of within_text
Behavior: SEARCHB returns #NUM! error because the search cannot begin at a byte position beyond the text length.
Solution: Use validation: =IF(start_num > LENB(within_text), "Out of range", SEARCHB(find_text, within_text, start_num)) before executing the search
Always verify start_num is within valid range when using dynamic calculations or user input
Mixed single-byte and multibyte characters in the same string
Behavior: SEARCHB correctly counts bytes regardless of character type. A string with 5 English letters (5 bytes) followed by 2 Chinese characters (6 bytes) totals 11 bytes.
Solution: Use LENB to verify total byte count and calculate positions accurately. Test formulas with representative sample data containing both character types.
This is common in international datasets and requires careful byte position calculation to avoid misalignment
Limitations
- •SEARCHB cannot use wildcard characters (* or ?) for pattern matching; it performs only literal text matching. For pattern-based searching, use REGEX function in Excel 365 or alternative approaches with helper columns.
- •SEARCHB is case-insensitive and cannot be modified for case-sensitive searching. If case-sensitivity is required, use FIND function instead or combine EXACT with SEARCH in helper columns.
- •SEARCHB returns only the first occurrence's byte position; finding multiple occurrences requires nested formulas or helper columns with sequential start_num adjustments, making complex multi-match scenarios labor-intensive.
- •SEARCHB operates exclusively on text data and cannot directly search numeric values; numbers must be converted to text using TEXT() function first, adding an extra processing step to formulas.
Alternatives
Simpler to use, counts characters instead of bytes, sufficient for single-byte character sets and most English-language applications.
When: Use SEARCH when working exclusively with ASCII text or single-byte character sets. Ideal for standard business data that doesn't involve Asian languages or multibyte encoding systems.
Provides case-sensitive searching, counts characters like SEARCH, and offers more control over text matching precision.
When: Use FIND when you need case-sensitive matching or when distinguishing between uppercase and lowercase letters is critical. Combine with other functions for more sophisticated text processing.
Compatibility
✓ Excel
Since 2007
=SEARCHB(find_text, within_text, [start_num]) - Fully supported in Excel 2007, 2010, 2013, 2016, 2019, and Microsoft 365✗Google Sheets
Not available
✓LibreOffice
=SEARCHB(find_text, within_text, [start_num]) - Supported in LibreOffice Calc with identical syntax to Excel