LEFT, LEFTB functions

The LEFT and LEFTB functions in Excel are used to extract a specified number of characters from the beginning of a text string. The difference between them is how they handle text in single-byte and double-byte character sets (DBCS), such as languages like Chinese, Japanese, or Korean.

1. LEFT Function

The LEFT function returns the first specified number of characters from the left side of a text string. It counts characters, so it works with single-byte characters (standard text).

Syntax:

=LEFT(text, [num_chars])

Parameters:

• text (required): The text string from which you want to extract characters.
• num_chars (optional): The number of characters you want to extract starting from the left of the text string. If omitted, the default is 1, meaning it will return just the first character.

Key Points:

• LEFT counts characters, regardless of whether those characters are single-byte or double-byte.
• If the text is shorter than the number of characters specified, LEFT will return the entire text string.
• If you specify a num_chars value greater than the length of the text, Excel will return the entire string.

Example Usage of LEFT

1. Extracting First Few Characters

If cell A1 contains the text “Hello World” and you want to extract the first 5 characters, you would use:

=LEFT(A1, 5)

This will return:

Hello

2. Extracting Just One Character

If you want to extract just the first character from “Hello World”, use:

=LEFT(A1, 1)

This will return:

H

3. No num_chars Argument

If you don’t specify the num_chars argument, it defaults to 1:

=LEFT(A1)

This will return:

H

4. Extracting More Than Available Characters

If the text in cell A1 contains “Hi” and you request the first 10 characters:

=LEFT(A1, 10)

This will return:

Hi

Since the text only has 2 characters, LEFT returns the whole string.

2. LEFTB Function

The LEFTB function works similarly to LEFT, but it is designed for use with double-byte character sets (DBCS), such as languages like Chinese, Japanese, or Korean. It counts bytes, not characters, so it treats each double-byte character as two bytes.

Syntax:

=LEFTB(text, [num_bytes])

Parameters:

• text (required): The text string from which you want to extract characters.
• num_bytes (optional): The number of bytes you want to extract, starting from the left. If omitted, the default is 1 byte.

Key Points:

• LEFTB counts bytes, which means it works with double-byte character sets, where each character typically occupies two bytes.
• The num_bytes parameter specifies the number of bytes to return, so it may extract more or fewer characters depending on whether the characters are single-byte or double-byte.
• If num_bytes exceeds the length of the string in bytes, LEFTB will return the entire string.

Example Usage of LEFTB

1. Extracting First Few Bytes

If cell A1 contains the text “こんにちは World” (Japanese) and you want to extract the first 6 bytes, you would use:

=LEFTB(A1, 6)

This will return the first 6 bytes of the string, which may include part of a double-byte character (depending on the character encoding), and the result could be:

こん

2. Extracting More Bytes Than the String Contains

If the string in A1 has fewer than the number of bytes specified, LEFTB will return the entire string. For example, with the string “Hi” and specifying 10 bytes:

=LEFTB("Hi", 10)

This will return:

Hi

Since “Hi” is just 2 characters (2 bytes), it will return the entire text.

Key Differences Between LEFT and LEFTB

• Character vs Byte Counting:
  • LEFT works with characters, regardless of whether they are single-byte or double-byte.
  • LEFTB works with bytes, so it is useful for double-byte characters typically used in languages like Chinese, Japanese, and Korean. It counts 2 bytes for each double-byte character, which may result in extracting more or fewer characters based on the encoding.
• Use Case:
  • Use LEFT when working with standard text or single-byte characters.
  • Use LEFTB when dealing with double-byte characters in DBCS, such as when working with Asian languages.

Notes:

• Byte vs Character: If you’re working with text that consists of characters with variable byte lengths (like Chinese or Japanese text), LEFTB is the more appropriate function. However, for most text strings in languages that use single-byte characters (like English), LEFT will suffice.
• Compatibility: LEFTB is more commonly used in systems where DBCS characters are used, such as in certain languages or regions (e.g., East Asia).
• Unicode: Modern Excel versions support Unicode, so the behavior of LEFT and LEFTB is important primarily in legacy systems or specific locales.

Related Functions:

• RIGHT and RIGHTB: These functions work similarly to LEFT and LEFTB, but they extract characters from the right side of a text string.
• MID and MIDB: These functions allow you to extract a substring from the middle of a text string, either by character or by byte.

The LEFT and LEFTB functions are helpful for extracting specific portions of a text string, with LEFT handling standard text and LEFTB dealing with double-byte character sets.