FINDB Formula: Complete Guide to Byte-Based Text Search in Excel
=FINDB(find_text, within_text, [start_num])The FINDB function is an advanced text search tool in Excel that locates the position of one text string within another, measuring position in bytes rather than characters. This distinction becomes critically important when working with multibyte character sets, particularly in Asian languages like Chinese, Japanese, and Korean where each character may occupy multiple bytes in the system's encoding. Unlike its counterpart FIND, which counts characters, FINDB provides byte-level precision essential for technical data processing and international applications. Understanding FINDB is crucial for professionals handling multilingual datasets, database administrators managing character encoding, and developers integrating Excel with systems that require byte-position information. The formula returns the starting byte position of the search text, enabling precise text manipulation and validation tasks. When combined with byte-aware functions like LEFTB, MIDB, and RIGHTB, FINDB becomes a powerful tool for sophisticated text processing workflows that maintain data integrity across different character encoding systems.
Syntax & Parameters
The FINDB function uses the syntax =FINDB(find_text, within_text, [start_num]) with three parameters working together to locate text at the byte level. The find_text parameter specifies the exact text string you're searching for—this is case-sensitive, meaning "Apple" and "apple" are treated as different values. The within_text parameter contains the larger text string where the search occurs; this is your source data. The optional start_num parameter indicates which byte position to begin searching from, allowing you to skip earlier portions of the text and find subsequent occurrences. If omitted, start_num defaults to 1, beginning the search at the first byte. Critically, FINDB returns the byte position where find_text begins within within_text. For single-byte characters (English letters, numbers, standard punctuation), this aligns with character position. However, with multibyte characters common in CJK languages, byte positions differ significantly from character counts. For example, a Chinese character might occupy 3 bytes, so the third character's position in bytes could be 7 or 9 rather than 3. If the text is not found, FINDB returns the #VALUE! error. Understanding byte calculations is essential: always verify your character encoding and test formulas with sample data containing your actual character types before deploying in production workflows.
find_textwithin_textstart_numPractical Examples
Finding Product Code in Invoice Text
=FINDB("SKU-2024", A1)This formula searches for the exact text 'SKU-2024' within cell A1, returning the byte position where this product code begins. If A1 contains 'Invoice: SKU-2024 Qty: 100', the formula returns 10 (assuming single-byte characters). This position can then be used with MIDB to extract additional data after the code.
Locating Japanese Product Names
=FINDB("製品", B2)When searching for multibyte characters like Japanese kanji, FINDB correctly counts bytes rather than characters. Each Japanese character typically occupies 3 bytes in UTF-8 encoding. If B2 contains 'カテゴリー: 製品名', FINDB returns the byte position accounting for the preceding characters' byte lengths, not just their character count.
Finding Second Occurrence with start_num
=FINDB("ERROR", C3, FINDB("ERROR", C3) + 1)This nested formula first finds the first occurrence of 'ERROR', adds 1 to that position to start searching from the next byte, then searches for the second occurrence. This technique works for finding subsequent occurrences by progressively incrementing the start position. If C3 contains 'ERROR in module, ERROR in database', the first FINDB returns 1, and the nested formula returns 22.
Key Takeaways
- FINDB provides byte-position search essential for multibyte character sets and international data processing where character-based functions fail
- Unlike FIND which counts characters, FINDB counts bytes—critical distinction when working with CJK languages and special encodings
- Combine FINDB with byte-aware functions (LEFTB, MIDB, RIGHTB) for precise text extraction without character truncation issues
- Always use error handling (IFERROR) and test with production data containing your actual character types before deploying FINDB formulas
- FINDB is case-sensitive and requires exact text matching; for flexible searching, consider SEARCH (case-insensitive) or REGEX (pattern matching) alternatives
Pro Tips
Always test FINDB formulas with sample data containing your actual character types (especially multibyte characters) before deploying to production. Byte positions vary dramatically between single-byte and multibyte encodings.
Impact : Prevents runtime errors and ensures formulas work correctly with your specific data, avoiding costly mistakes in automated workflows processing international data.
Combine FINDB with IFERROR to handle cases where text isn't found: =IFERROR(FINDB(find_text, within_text), "Not found"). This prevents #VALUE! errors from breaking dependent formulas.
Impact : Improves formula robustness and creates user-friendly error messages instead of cryptic error codes, making spreadsheets more maintainable and professional.
Use FINDB with SUBSTITUTE to find and count occurrences: Count = (LEN(A1) - LEN(SUBSTITUTE(A1, "search", ""))) / LEN("search"). For byte-aware counting with multibyte characters, this technique remains reliable.
Impact : Enables sophisticated text analysis and validation without complex nested formulas, streamlining data quality checks and pattern analysis.
When working with multibyte characters, verify your Excel file encoding is UTF-8 or UTF-16 to ensure FINDB calculations remain consistent. Encoding mismatches cause unpredictable byte position results.
Impact : Eliminates mysterious formula inconsistencies when sharing files across systems with different default encodings, ensuring reliable international data processing.
Useful Combinations
Extract Text After Found Position Using MIDB
=MIDB(A1, FINDB(":", A1) + 1, 10)This combination finds the colon character's byte position using FINDB, adds 1 to start after the colon, then uses MIDB to extract 10 bytes of text following that position. Perfect for parsing delimited data where you need content after a specific marker. For example, extracting the value portion from 'Status: Active' would return 'Active'.
Find and Replace with Position Calculation
=IF(ISERROR(FINDB("OLD", B1)), B1, SUBSTITUTE(B1, "OLD", "NEW"))Combines FINDB with ISERROR and SUBSTITUTE to conditionally replace text only if the old text exists. FINDB checks presence, and SUBSTITUTE performs the replacement. This prevents errors when the search text doesn't exist and provides clean conditional logic for data cleaning workflows.
Extract Text Before Found Position Using LEFTB
=LEFTB(C1, FINDB("-", C1) - 1)Uses FINDB to locate a hyphen, then LEFTB extracts everything before it by subtracting 1 from the found position. Useful for parsing codes like 'DEPT-2024-001' to extract just 'DEPT'. Byte-accurate extraction ensures multibyte characters are handled correctly without truncation.
Common Errors
Cause: The find_text string does not exist anywhere in within_text, or one of the parameters contains invalid data types like numbers instead of text. FINDB is case-sensitive, so searching for 'apple' in text containing 'Apple' will return #VALUE!.
Solution: Verify that find_text actually exists in within_text using exact case matching. Convert parameters to text using TEXT() function if needed. Use IFERROR(FINDB(...), "Not found") to handle missing values gracefully in production formulas.
Cause: This error appears when Excel doesn't recognize 'FINDB' as a valid function name. This typically occurs in older Excel versions (pre-2007), regional Excel installations with different function names, or when the function name is misspelled as 'FINDBY' or 'FIND_B'.
Solution: Confirm you're using Excel 2007 or later. Check your regional Excel settings—some localized versions use different function names. Verify the exact spelling 'FINDB' without spaces or special characters. Consider using FIND as an alternative if FINDB is unavailable.
Cause: This error occurs when the formula references cells that have been deleted or moved, typically in nested formulas where FINDB references are broken. It can also appear if start_num references a deleted cell.
Solution: Check all cell references in your formula to ensure they still exist and contain valid data. Use absolute references ($A$1) instead of relative references when copying formulas across rows to prevent reference breaking. Rebuild the formula using the current cell references.
Troubleshooting Checklist
- 1.Verify find_text exists in within_text with exact case matching—FINDB is case-sensitive and won't find 'apple' in 'Apple'
- 2.Confirm Excel version is 2007 or later; FINDB doesn't exist in earlier versions—check regional function name variations
- 3.Check that parameters are text format, not numbers or other data types; use TEXT() conversion if needed
- 4.Validate start_num is a positive integer less than the byte length of within_text; excessive start_num values cause #VALUE! errors
- 5.Test with actual multibyte character data to verify byte position calculations match expectations; encoding differences affect results
- 6.Ensure cell references haven't been deleted or moved; broken references cause #REF! errors in nested formulas
Edge Cases
Empty string as find_text parameter
Behavior: FINDB returns 1, indicating the empty string is found at the start of any text. This is technically correct but often unexpected in practical applications.
Solution: Add validation to reject empty find_text: =IF(LEN(find_text)=0, "Error: Empty search", FINDB(find_text, within_text))
Consider whether empty string searches make sense in your workflow; usually they represent data entry errors
within_text is shorter than find_text in byte length
Behavior: FINDB returns #VALUE! because the search text cannot possibly fit within the source text, regardless of character count appearing sufficient.
Solution: Validate text lengths before searching: =IF(LEN(within_text) < LEN(find_text), "Text too short", FINDB(find_text, within_text))
This edge case is particularly problematic with multibyte characters where byte length significantly exceeds character count
Mixed single-byte and multibyte characters in same string
Behavior: FINDB correctly calculates byte positions accounting for variable character widths. A string with 5 English letters and 3 Chinese characters occupies 14 bytes (5×1 + 3×3), not 8.
Solution: Test formulas with actual mixed-character data to verify byte calculations. Use LEN() for character count and LENB() for byte count to debug position mismatches.
This is common in international business data and requires careful formula testing to avoid off-by-one errors
Limitations
- •FINDB only returns the first occurrence's position; finding multiple occurrences requires nested formulas with incrementing start_num values, creating complex and performance-heavy calculations
- •Case-sensitive matching means 'Apple' and 'apple' are treated as different values—there's no parameter to enable case-insensitive searching like SEARCH provides
- •No wildcard support; FINDB requires exact text matching and cannot search for patterns like 'A*B' or 'A?B', limiting flexibility for pattern-based text discovery
- •Byte-position results are encoding-dependent; formulas may return different results if file encoding changes or is transferred between systems with different default encodings, reducing portability
Alternatives
Simpler character-based search that works for most English text. Returns character position rather than byte position, making results more intuitive for single-byte character sets.
When: Use FIND when working exclusively with English text, numbers, and standard punctuation where character and byte positions are identical. FIND is also case-insensitive variant of SEARCH.
Case-insensitive text search supporting wildcards (* and ?). Returns character position rather than bytes, providing more flexible pattern matching capabilities.
When: Use SEARCH when you need case-insensitive matching or wildcard pattern support. Best for scenarios where exact case matching isn't critical and you're working with single-byte character sets.
Powerful pattern matching using regular expressions. Supports complex search criteria, multiple conditions, and extraction without byte/character limitations.
When: Use REGEX in Excel 365 for sophisticated pattern matching, validation, and extraction tasks. Ideal for finding patterns that FINDB cannot handle, such as email validation or complex text parsing.
Compatibility
✓ Excel
Since 2007
=FINDB(find_text, within_text, [start_num]) - Fully supported in Excel 2007, 2010, 2013, 2016, 2019, and Excel 365✗Google Sheets
Not available
✓LibreOffice
=FINDB(find_text, within_text, [start_num]) - Supported in LibreOffice Calc with identical syntax to Excel