A to Z of Excel Functions: The MIDB Function

Welcome back to our regular A to Z of Excel Functions blog. Today we look at the MIDB function.

The MIDB function

The MIDB function returns a specific number of characters from a text string, starting at the position you specify, based upon the number of bytes you specify.

The MIDB function employs the following syntax to operate:

The MIDB function has the following arguments:

• text: this is required and represents the text string that contains the characters you want to extract
• start_number: this is also required and specifies the position of the first character you want to extract from text.  This is based upon bytes in text, so that the first byte has start_number 1, and so on
• number_of_characters: this mandatory argument specifies the number of bytes you want MID to return from the text.

It should be further noted that:

• if number_of_bytes is negative, MIDB returns the #VALUE! error value
• this function may not be available in all languages.  MIDB counts two (2) bytes per character only when a DBCS language is set as the default language (the languages that support DBCS include Japanese, Chinese (Simplified), Chinese (Traditional), and Korean
• otherwise, MIDB behaves the same as MID, counting one (1) byte per character.

For example, =MIDB("中国香港",3,2) is equal to "国".  MIDB returns the second character only, because each character is counted as two (2) bytes and the second character begins at the third byte.

However, =MID("中国香港",3,2) is equal to "香港" as MID returns the next two (2) characters from the third character onwards, because each character is counted as one (1).  MID returns these two characters no matter what the default language setting is on your computer.

We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.

A full page of the function articles can be found here.