Master the MIDB Function: Extract Text Based on Byte Position in Excel
=MIDB(text, start_num, num_bytes)The MIDB function is an advanced text manipulation tool in Excel designed specifically for working with multibyte character sets, particularly in Asian languages like Chinese, Japanese, and Korean. Unlike the standard MID function which counts characters, MIDB counts bytes, making it essential for precise text extraction in international spreadsheets. This distinction becomes critical when dealing with double-byte character encoding where each character may occupy multiple bytes in the system's memory. Understanding MIDB is crucial for data analysts, international business professionals, and anyone managing multilingual datasets. The function extracts a specific number of bytes from a text string starting at a designated byte position, providing granular control over text manipulation that character-based functions cannot achieve. Whether you're processing customer names from Asian markets, cleaning imported data, or building sophisticated text parsing systems, MIDB offers the precision needed for accurate results in byte-sensitive environments.
Syntax & Parameters
The MIDB function follows the syntax: =MIDB(text, start_num, num_bytes). The 'text' parameter is required and represents the source string from which you want to extract content. This can be a cell reference, a text string in quotes, or a formula result that produces text. The 'start_num' parameter specifies the starting position measured in bytes (not characters), where counting begins at byte 1. This is critical for multibyte characters where a single character might equal 2, 3, or 4 bytes depending on the encoding. The 'num_bytes' parameter defines how many bytes to extract from the starting position. If start_num is less than 1, MIDB returns a #VALUE! error. If num_bytes is negative or zero, it returns an empty string. When the combined start and length exceeds the total bytes available, MIDB extracts only the available bytes without error. This function is particularly valuable when working with UTF-8 or other multibyte encodings where character-based extraction would produce incorrect results.
textstart_numnum_bytesPractical Examples
Extracting Japanese Customer Name
=MIDB(A2,1,4)Starting at byte 1, this formula extracts exactly 4 bytes from the customer name. If A2 contains '山田太郎', the result would be '山田' (the first two characters/4 bytes), effectively separating the family name from the given name.
Extracting Middle Portion of Product Code
=MIDB(B3,5,6)This formula begins extraction at byte 5 and captures the next 6 bytes. In a product code like 'ABC田中XYZ', if '田中' starts at byte 5, this would extract those characters plus surrounding content, useful for parsing complex product identifiers.
Processing Chinese Address Data
=MIDB(C5,7,6)Starting from byte 7, this formula extracts 6 bytes which typically represents 3 Chinese characters. For an address like '北京市朝阳区建国路1号', this would isolate the district portion '朝阳区' for geographic categorization and reporting.
Key Takeaways
- MIDB counts bytes instead of characters, essential for accurate extraction from multibyte character sets used in Asian languages
- Always use LENB() to verify available bytes and validate start positions before building MIDB formulas to prevent extraction errors
- MIDB is identical to MID for single-byte English text, but provides necessary precision for multilingual or mixed-encoding data
- Combine MIDB with error handling (IFERROR) and validation functions (IF, AND) to build robust formulas for production environments
- Document byte positions and character mappings in helper columns or comments to maintain formula clarity and enable future modifications
Pro Tips
Always use LENB() to verify total bytes available before calculating start positions and byte counts. Create a helper column showing LENB values for reference data.
Impact : Prevents extraction errors and helps you understand the byte structure of your multibyte text, enabling accurate formula design and reducing debugging time.
When working with mixed single-byte and multibyte characters, test your formula on sample data first. Use MIDB with small byte ranges to identify exact character boundaries.
Impact : Ensures accuracy when processing real data and helps you understand how your specific encoding system assigns bytes to characters, critical for production formulas.
Combine MIDB with IFERROR to handle edge cases gracefully: =IFERROR(MIDB(A1,start,length),"Extraction failed"). This prevents formula errors from breaking entire reports.
Impact : Improves spreadsheet reliability and user experience by providing meaningful error messages instead of cryptic error codes, especially important in shared workbooks.
Document byte positions in comments or adjacent cells when building complex MIDB formulas. Include the character representation alongside byte positions for clarity.
Impact : Makes formulas maintainable and understandable for other users. Future modifications become easier when byte-to-character mappings are clearly documented.
Useful Combinations
Extract and Clean Multibyte Text with TRIM
=TRIM(MIDB(A1,5,8))Combines MIDB for byte-precise extraction with TRIM to remove leading and trailing spaces from the result. This is particularly useful when extracting data from fixed-width multibyte fields where padding spaces are common in legacy systems.
Conditional Extraction Using IF and LENB
=IF(LENB(A1)>=10,MIDB(A1,1,6),"Text too short")Uses LENB to validate that the source text has sufficient bytes before attempting extraction. This prevents errors when processing variable-length data and provides user-friendly feedback when data doesn't meet extraction requirements.
Concatenate Multiple MIDB Extractions
=CONCATENATE(MIDB(A1,1,4)," ",MIDB(A1,5,6)," ",MIDB(A1,11,4))Extracts multiple byte ranges from a single text string and reassembles them with custom separators. Useful for reformatting fixed-format multibyte data, such as converting '山田太郎東京' into '山田 太郎 東京' for better readability.
Common Errors
Cause: The start_num parameter is less than 1, or num_bytes is a negative number. For example: =MIDB(A1,0,5) or =MIDB(A1,3,-2)
Solution: Ensure start_num is at least 1 and num_bytes is zero or positive. Use validation: =IF(AND(start_num>=1,num_bytes>=0),MIDB(A1,start_num,num_bytes),"Invalid parameters")
Cause: The text parameter references a deleted cell or an invalid range. This typically occurs when copying formulas and the source range is inadvertently deleted.
Solution: Verify the cell reference exists and contains data. Use Find & Replace to locate broken references, or use IFERROR to handle missing data gracefully: =IFERROR(MIDB(A1,1,4),"Data unavailable")
Cause: Byte counting confusion: treating characters as bytes when working with multibyte characters, or incorrect start position calculation. For example, if a character is 2 bytes but you specify start_num as if it were 1 byte.
Solution: Use LEN() to count characters and LENB() to count bytes for verification. Create a reference table: =MIDB(A1,1,LENB(A1)) to extract all bytes, then adjust start positions based on actual byte values, not character count.
Troubleshooting Checklist
- 1.Verify that start_num is at least 1 and num_bytes is not negative using =IF(AND(start_num>=1,num_bytes>=0),"Valid","Invalid")
- 2.Confirm total available bytes using =LENB(text) to ensure start_num + num_bytes doesn't exceed total length
- 3.Check that the text parameter correctly references the source cell and contains actual data, not formulas with errors
- 4.Verify encoding: ensure your text uses multibyte encoding (UTF-8, Shift-JIS, etc.) and not single-byte ASCII where MID would be more appropriate
- 5.Test with MIDB(text,1,LENB(text)) to extract all bytes and confirm the complete text displays correctly
- 6.Use LENB on individual characters to map byte positions accurately, especially when mixing languages or special characters
Edge Cases
Empty text string passed to MIDB
Behavior: Returns empty string without error. =MIDB("",1,5) produces ""
Solution: Use IFERROR or IF to check for empty strings: =IF(LEN(A1)=0,"No data",MIDB(A1,1,5))
This is expected behavior but should be handled in production formulas to provide meaningful feedback
Start position exactly at the last byte
Behavior: If start_num points to the final byte and num_bytes=1, returns that single byte. If num_bytes>1, returns only that final byte.
Solution: Validate that start_num + num_bytes <= LENB(text) + 1 to ensure extraction stays within bounds
This behavior is consistent with Excel's design but requires careful boundary checking in complex formulas
Mixed encoding within single cell (rare but possible)
Behavior: MIDB extracts bytes regardless of encoding transitions, potentially splitting characters incorrectly if byte boundaries don't align with character boundaries
Solution: Avoid mixed encodings; standardize text to single encoding before extraction. If unavoidable, use helper columns to identify safe byte boundaries
This is a data quality issue rather than formula issue; prevention through data validation is the best approach
Limitations
- •MIDB cannot distinguish between different multibyte encodings automatically; it treats all bytes equally regardless of whether they represent valid character sequences in their encoding system
- •No built-in byte position finder; you must manually calculate or use helper columns with LENB() to identify correct start positions, making formula development more complex than character-based extraction
- •Performance can degrade when processing very large text strings with complex byte calculations; optimization requires careful formula design and potentially VBA for high-volume operations
- •Limited error messaging; MIDB provides only #VALUE! errors without indicating specific issues like invalid byte positions or encoding problems, requiring additional validation logic for troubleshooting
Alternatives
Simpler syntax and more intuitive for character-based extraction. Works seamlessly with single-byte text and is more widely understood.
When: Use MID when working exclusively with English text or when character position is more meaningful than byte position. Not suitable for multibyte character sets where precision is required.
More specialized for extracting from the beginning or end of text strings. Simpler when you only need left or right portions without middle extraction.
When: Use LEFTB to extract the first N bytes or RIGHTB for the last N bytes. Combine them with other functions for more complex extraction patterns without needing precise start positions.
Provides pattern-based extraction with greater flexibility. Can extract text based on patterns rather than fixed byte positions.
When: Use REGEX when you need conditional extraction based on patterns, such as extracting all characters between specific delimiters or matching specific formats in multibyte text.
Compatibility
✓ Excel
Since 2007
=MIDB(text, start_num, num_bytes) - Identical syntax across all versions from Excel 2007 through Excel 365✓Google Sheets
=MIDB(text, start_num, num_bytes) - Fully supported with identical behavior to ExcelGoogle Sheets provides complete MIDB functionality for multibyte character extraction, making it suitable for collaborative international projects
✓LibreOffice
=MIDB(text, start_num, num_bytes) - Supported but with potential encoding limitations depending on system configuration