How to Use MIDB Function in Google Sheets

This guide will discuss how to use the MIDB function in Google Sheets.

When we need to return a string section starting at a given character and up to a specified number of bytes, we can easily do this using the MIDB function in Google Sheets.

The rules for using the MIDB function in Google Sheets are the following:

• The MIDB function returns the same values as the MID function if the input string has only single-byte characters.
• The inputted value of the starting_at argument must be greater than or equal to 1. 
• The inputted value of the extract_length_bytes argument must be greater than or equal to 0.
• The MIDB function only works with bytes, not characters.
• Each character may require two or more bytes when working with double-byte character sets. 
• If starting_at is greater than the length of the string, the function will return an empty string.

The MIDB function is a powerful text manipulation function that allows us to extract a specific part of a text string based on the starting position and the number of bytes we want to extract.

This function is particularly useful when working with text data that has non-ASCII characters. For example, languages that use double-byte character sets, like Japanese or Korean. 

In this guide, we will provide a step-by-step tutorial on how to use the MIDB function in Google Sheets. Additionally, we will explore the syntax and a real example of using the function.

Great! Let’s dive right in.

The Anatomy of the MIDB Function

The syntax or the way we write the MIDB function is as follows:

=MIDB(string,starting_at,extract_length_bytes)

• = the equal sign is how we begin any function in Google Sheets.
• MIDB() is our MIDB function. This function is used to extract a portion of a text string, starting at a specific character position for a given length in bytes.
• string is a required argument. This refers to the string from which we want to extract a section.
• starting_at is also a required argument. This refers to the position in the input string to start extracting from. The first character is at position 1.
• extract_length_bytes is another required argument. This refers to the number of bytes the extracted string should have.

Common Mistakes in Using MIDB Function

The MIDB function has a straightforward syntax making it simple to use. However, we still need to be careful when using some things to ensure the function properly works.

Firstly, we may have used the wrong starting_at or extract_length_bytes. This can result in an incorrect or incomplete text extraction.

Secondly, remember that the MIDB function works with bytes and not characters. We must always specify the extract_length_bytes argument based on the number of bytes instead of the number of characters. 

Thirdly, we must be careful when working with double-byte character sets. If we are working with text data with only single-byte characters, we can use the MID function for a more straightforward method.

However, we must use the MIDB function when working with double-byte character sets to avoid incorrect results.

Lastly, check the syntax of the formula. Ensure the syntax of the function call is correct, including the proper placement of commas, the use of parentheses, and quotation marks.

A Real Example of Using MIDB Function in Google Sheets

Let’s say we have a data set containing a text string. We want to extract a portion of the text string starting at a specific character position and for a given length in bytes.

Our initial data set would look like this:

For example, the text string “Hello, 结尾!” is a mixture of English and Chinese characters. Let’s say we want to extract the Chinese characters. 

We can use the formula below to extract a portion of the text string:

=MIDB(B3,8,6)

The formula above first shows the cell containing the text string we want to extract from. Then, we inputted 8 as our starting_at argument since the portion we want to extract starts after 8 bytes. 

Lastly, we inputted 6 as our extract_length_bytes argument because the extracted string has 6 bytes. 

We extracted 6 bytes starting from the 8th character position resulting in the output “结尾!” which means friend in Chinese. 

Remember that the MIDB function works with byte positions and not character positions. It’s important to be aware of the specific encoding of the text string we are working with to ensure accurate extraction.

Our final data set would look like this:

You can make your own copy of the spreadsheet above using the link below. 

Amazing! Now we can dive into the steps of using the MIDB function in Google Sheets.

How to Use MIDB Function in Google Sheets

1. First, we will select an empty cell to type in our formula. To start, we will type in an equal sign and the function name. Our formula would start with “=MIDB(”.

2. Then, we will simply select the cell containing the text string we want to extract from. Our formula would become “=MIDB(B3”.

3. Next, we will input the number of bytes where the extraction should begin. In this case, we will extract after 8 bytes. Our formula would become “=MIDB(B3,8”.

4. Lastly, we will input the number of bytes the extracted string should have. In this example, it should have 6 bytes. Our final formula would be “=MIDB(B3,8,6)”.

5. We will press the Enter key to return the result. 

And tada! We have successfully used the MIDB function in Google Sheets.

You can apply this guide whenever you need to return a section of a text string starting at a given character and up to a specified number of bytes. You can now use the MIDB function and the various other Google Sheets formulas available to create great worksheets that work for you.

FAQs

1. What do bytes mean in Google Sheets?
Bytes refer to all sorts of data, including letters, numbers, and symbols. In this case, we will count all the symbols and spaces as bytes to ensure accurate extraction.

2. How do I accurately count bytes in Google Sheets?
The LENB function returns the number of bytes for double-byte characters in a string. We can utilize this function to accurately count the bytes in a text string.

3. What other functions can I combine with the MIDB function?
We can combine the MIDB function with other text manipulation functions, such as the LEFTB function, the RIGHTB function, and the FINDB function. This allows us to perform more complex text operations. 

That’s pretty much it! Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.