LEN, LENB functions

The LEN and LENB functions in Excel are used to count the number of characters in a text string. The difference between the two is how they handle single-byte versus double-byte characters (DBCS, such as Chinese, Japanese, or Korean characters).

1. LEN Function

The LEN function returns the number of characters in a text string, including spaces, punctuation, and other characters. It is used with single-byte characters (standard text), and it counts characters in a string based on the character length, regardless of whether the characters are single-byte or double-byte.

Syntax:

=LEN(text)

Parameters:

• text (required): The text string or cell reference for which you want to count the number of characters.

Key Points:

• LEN counts characters, not bytes.
• It includes all characters, including spaces, punctuation marks, and special characters.
• The result is the number of characters in the string, and this includes single-byte and double-byte characters, where each double-byte character counts as one character.

Example Usage of LEN

1. Counting Characters in a Text String

If cell A1 contains the text “Hello World”, the formula:

=LEN(A1)

This will return:

11

The function counts all the characters, including the space, so the result is 11.

2. Including Spaces and Punctuation

If cell A2 contains the text “Excel, 2025!”, the formula:

=LEN(A2)

This will return:

14

The spaces, comma, and exclamation mark are all counted as characters.

3. Counting Characters in a String with Special Characters

If cell A3 contains the text “Hello@#^!”, the formula:

=LEN(A3)

This will return:

9

All special characters are also counted.

2. LENB Function

The LENB function is similar to LEN, but it counts bytes instead of characters. It is specifically designed for double-byte character sets (DBCS), such as those used in languages like Chinese, Japanese, and Korean. In DBCS, each character may use two bytes, while in LEN, they would count as a single character.

Syntax:

=LENB(text)

Parameters:

• text (required): The text string or cell reference for which you want to count the number of bytes.

Key Points:

• LENB counts bytes, not characters.
• It is mainly used for double-byte characters (DBCS), where each character may take more than one byte (typically two bytes per character).
• If the text is in single-byte characters, LENB behaves similarly to LEN.
• For DBCS text, LENB will return a higher count than LEN because it counts each double-byte character as two bytes.

Example Usage of LENB

1. Counting Bytes in a Text String (DBCS)

If cell A1 contains the text “こんにちは World” (Japanese), which includes both double-byte and single-byte characters, the formula:

=LENB(A1)

This will return:

24

Explanation:

• “こんにちは” (Japanese) consists of 5 characters, each of which is a double-byte character, so each of these 5 characters counts as 2 bytes, for a total of 10 bytes.
• “World” is a single-byte string (English), so it counts as 5 bytes.
• Thus, the total byte count is 10 + 5 + 1 (for the space), resulting in 24 bytes.

2. Counting Bytes in a Text String with Only Single-Byte Characters

If cell A2 contains “Hello World” (English), the formula:

=LENB(A2)

This will return:

11

Since all characters are single-byte characters, the byte count is equal to the character count. The result is 11 bytes, which matches the result of LEN.

3. Counting Bytes in a Text String with Mixed Content

If cell A3 contains “こんにちは 123”, where “こんにちは” is in Japanese (double-byte) and “123” is in English (single-byte), the formula:

=LENB(A3)

This will return:

16

Explanation:

• “こんにちは” (Japanese) consists of 5 characters, each of which is 2 bytes, totaling 10 bytes.
• ” 123″ (English) consists of 4 characters (the space and the digits), each being 1 byte.
• Thus, the total byte count is 10 + 4 = 16 bytes.

Key Differences Between LEN and LENB

• Character vs Byte Counting:
  • LEN counts characters, which is useful for strings in single-byte character sets (like English).
  • LENB counts bytes, which is more appropriate for double-byte character sets (DBCS), used in languages like Chinese, Japanese, and Korean.
• Use Case:
  • Use LEN when you need to count characters, especially in languages that use single-byte encoding.
  • Use LENB when dealing with double-byte characters, where characters may occupy more than one byte, especially in East Asian languages.

Notes:

• Inconsistent Results: If you’re working with DBCS text, LENB will give you a higher count than LEN because DBCS characters use multiple bytes.
• Single-byte Text: If your text is entirely in single-byte characters (like most Western languages), LEN and LENB will give you the same result.
• Byte Calculation: When working with LENB, consider that double-byte characters are counted as 2 bytes, so the byte count may appear to be much larger than the character count.

Related Functions:

• MID and MIDB: These functions allow you to extract a substring from a string, either by character (with MID) or by byte (with MIDB).
• RIGHT and RIGHTB: These functions are similar to LEN and LENB, but they extract characters or bytes from the right side of a text string.
• TRIM: Removes extra spaces from a text string, but only works on single-byte characters.

The LEN and LENB functions are essential for counting characters or bytes in text, especially when dealing with multi-byte character sets, ensuring you handle text data correctly for different languages and formats.