LEFTB in Google Sheets

What is LEFTB in Google Sheets?

The LEFTB function gives you the the left part of a string up to a specified number of bytes. LEFTB in Google Sheets returns the first character or characters from the left side of a text string, but it measures the length in bytes instead of characters. It is very useful when working with double-byte character sets like Japanese, Chinese, or Korean. IN these languages, one character may take up two bytes. When it comes to regular English text, LEFTB behaves the same as the LEFT function.

For example, we have a text string “東京Japan” in cell A1. Now, to extract the first 4 bytes, we use the following formula:

=LEFTB(A1, 4)

This formula would return “東” because “東” takes 2 bytes and “京” also takes 2 bytes. Thus, the two letters total take 4 bytes. If the text were standard English and there is a word like “Lion,” then =LEFTB(A1, 4) would return “Lion”, since each English character counts as 1 byte.

Syntax

Now that we have a brief introduction to this useful function, let us look at the LEFTB in Google Sheets formula:

=LEFTB(string, num_of_bytes)

Here, the arguments mean the following.

• string     – it is the string from which the left portion will be returned.
• num_of_bytes (Optional)- The number of bytes to return from the left side of `string`.

How To Use LEFTB Function in Google Sheets?

As we know, the LEFTB function is used to extract a specific number of bytes from the left side of a text string. Unlike the standard LEFT function in Google Sheets, LEFTB is especially useful when working with double-byte character sets like Japanese, where a single character may occupy 2 bytes. This allows accurate extraction of text when we have multilingual datasets.

You can use the LEFTB function in two ways:

• By manually typing LEFTB
• By selecting it from the Google Sheets menu

Using the LEFTB Function Manually

Let’s walk through an example to see how LEFTB works when we enter the function manually.

Suppose cell A1 contains the text string “화이팅 hwaiting“. We want to extract the first 4 bytes from this string.

Step 1: Enter the text “화이팅 hwaiting” into cell A1.

Step 2: Select an empty cell like B1 to display the result. Start typing the function:

=LEFTB(

Step 3: Supply the required arguments inside parentheses. You can either enter the string directly or refer to a cell. For example:

=LEFTB(A1, 4)

Here, A1 is the text string, 4 is the number of bytes to extract.

Step 4: Press Enter. The cell will display “화이”, because each of the Korean characters “화” and “이” takes 2 bytes, totaling 4 bytes.

Using LEFTB From the Menu

1. Click on any empty cell.
2. Navigate to Insert → Function → Text.
3. From the list, select LEFTB.
4. Fill in the required arguments (text and number of bytes) and press Enter.

By using the LEFTB function, you can accurately extract portions of text, especially in multilingual datasets or when working with characters that occupy multiple bytes. This ensures that text manipulation is accurate without any unexpected truncation or errors.

Examples

Let us look at some interesting examples to understand in detail of how this function works.

Example #1 – Extract Multilingual Product Descriptions

A retailer has some product descriptions in cells A1 to D5. They contain Japanese characters and some other language characters. They want to extract the first 4 bytes from these strings to display just the different language portion in a catalog.

Step 1: Open a Google Sheet and enter the text strings in cell A2 to A5.

Step 2: Use the LEFTB formula as shown below:

=LEFTB(A2, 4)

Press Enter.

Step 3: The formula returns “東京” because each Japanese character occupies 2 bytes, totaling 4 bytes.

Drag the formula down for the other two products.

Step 4: In the final product, we need only three bytes. So, we re-enter the formula as:=LEFTB(A5, 3).

This helps the retailer accurately extract multilingual product names without truncating characters incorrectly, ensuring that catalog or inventory listings display text correctly.

Example #2 – Combining LEFTB with LENB Function

A company stores multilingual product codes in the format “東京1234”, “北京5678”, “서울9012” in a Google Sheet. Each code starts with double-byte city characters followed by numeric digits. The company wants to extract only the city portion, regardless of the number of bytes in the numeric part.

Step 1: Enter the product codes into column A.

Step 2: To extract only the city portion, we need to know the number of bytes for the numeric part using LENB. We first calculate the total bytes of the numeric part:

=LENB(“1234”)

This returns 4, because each digit counts as 1 byte.

Use the LEFTB function to extract everything except the numeric bytes. In cell B2, enter:

=LEFTB(A2, LENB(A2)-LENB(“1234”))

Step 3: Press Enter. The result will be “東京” for the first entry. Similarly, “北京” and “서울” will be extracted for the other rows when you drag the formula down.

By combining LEFTB and LENB, we can dynamically extract variable-length multilingual prefixes from text strings without accidentally cutting off double-byte characters. This approach is especially useful for product codes.

Example #3 – Using LEFTB for Data Validation

A company receives product codes from multiple vendors, where each code must start with a 2-character prefix in Japanese or Chinese followed by numbers. To ensure all entries follow this rule, the company wants to use LEFTB and make a data validation check.

Step 1: Enter product codes received from vendors in Column A.

Step 2: Create a formula to validate if the first 4 bytes (2 double-byte characters) are non-numeric in column B. For this, we enter the following function.

=IF(ISTEXT(LEFTB(A2,4)),”Valid”,”Invalid”)

Step 3: Press Enter and drag down the formula. The results will be:

Step 4: Any entry not starting with the required 2-character prefix will show as Invalid, helping the company quickly flag incorrect or incomplete codes.

Using LEFTB with validation formulas helps businesses enforce data entry rules in multilingual environments. This ensures consistency across product codes, prevents vendor errors, and maintains clean records in the database.

Important Things to Note

1. LEFTB returns the same value as LEFT if the input string has only single byte characters.
2. Here, the argument num_of_bytes must be greater than or equal to zero.
3. If the number of bytes argument is bigger that the length of the text in bytes, we get all the text. If the number of bytes is omitted, we get 1.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand LEFTB Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What is LEFTB Function in Google Sheets. We learn how to use LEFTB function in google sheets with its syntax and examples. You can learn more from the following articles. –

BYROW in Google Sheets

CHOOSECOLS in Google Sheets

CHOOSEROWS in Google Sheets