How to Use LENB Function in Google Sheets

This guide will explain how to use the LENB function in Google Sheets.

When setting up a database, it is best practice to know the estimated storage requirements for each field in a table. Even if a given field has a character limit, not all characters use the same number of bytes.

For example, the string “Hello” requires five bytes since each character uses one byte of storage. However, multilingual datasets can sometimes have characters that take up two bytes. Other special characters like emojis can also take up to 4 bytes.

To know the length of a string in bytes, users can apply the LENB function in Google Sheets. In this guide, we will provide a step-by-step tutorial on how to use the LENB function.

The Anatomy of the LENB Function

The syntax of the LENB function is as follows:

=LENB(string)

Let’s look at each argument to understand how to use the LENB function.

• The string argument refers to the string you want to find the length in bytes of.
• The LENB function returns an integer that corresponds to the number of bytes needed to represent the given string.
• If you want to find the number of characters in a string, you may use the LEN function instead.

A Real Example of the LENB Function in Google Sheets

Let’s explore a simple example where we can use the LENB function in Google Sheets.

In the table above, we have a dataset of string inputs that use a variety of different characters.

We can use the following formula to find the byte length of each string input:

=LENB(A2)

After using the LENB formula, we’ve determined that a character like ‘A’ or ‘!’ take up a single byte, and non-ASCII characters take up two or more bytes per individual character.

Finding the Required Byte Length for a Column

We can use the LENB function to determine the minimum bytes required to store each cell in a field.

In the example above, we have a list of customer information that we want to store in a database.

We can use the LENB function with the MAX and ARRAYFORMULA functions to find the largest values in each column in terms of bytes. We’ll use the following formula to find the required field length for the ID column:

=MAX(ARRAYFORMULA(LENB(A2:A201)))

We’ll need to wrap our LENB function with an ARRAYFORMULA to apply the LENB function for each cell in the range A2:A201. The result of the ARRAYFORMULA is then added as an input to the MAX function, which will determine the length of the cell with the largest string size.

In our example above, we determined that the id field requires a minimum of 3 bytes to hold all existing values. We can then apply the same formula and adjust the target range to find the required field length for each field.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to use the LENB function in Google Sheets.

How to  Use the LENB Function in Google Sheets.

1. Select the cell where you want to output the byte length of a string.
   
2. Type the LENB function and add the cell references containing the strings you want to measure.
   
3. Hit the Enter key to evaluate the LENB function.
   In this example, we’ve determined that the email ‘[email protected]’ requires 19 bytes.
   
4. We’ll use the Auto-Fill feature to find the byte length of the remaining data.
   
5. Given a range, we can use the LENB, MAX, and ARRAYFORMULA functions to find the number of bytes required to hold the longest string in terms of bytes.
   In the example above, we used the formula =MAX(ARRAYFORMULA(LENB($A$2:$A$201))) to find the required field length of the id field. 

These steps are all you need to know to start using the LENB function in Google Sheets.

FAQs

1. What is the difference between the LEN and LENB functions?
   The LEN and LENB functions in Google Sheets differ in how they measure the length of text. The LEN function counts the number of characters in a text string, while the LENB function counts the number of bytes used to represent those characters. This distinction is particularly important when handling non-ASCII characters, which typically require two bytes instead of one.
   
2. What is the purpose of finding out the number of bytes used to represent a text value?
   Finding the byte length of your text data can be important when you’re setting up a database for your data or when you intend to add the data to an existing system with specific byte limitations. 

To learn more about functions for strings in Google Sheets, you can read our post on how to clean up unnecessary spaces in your dataset.

That’s all for this guide! Check out our library of spreadsheet resources, tips, and tricks!