RIGHT, RIGHTB functions

The RIGHT and RIGHTB functions in Excel are used to extract a specified number of characters from the right end of a text string. The difference between the two functions is based on how they handle the byte length of the text, which becomes significant in languages that use double-byte characters (such as Chinese, Japanese, or Korean).

1. RIGHT Function

The RIGHT function extracts a specified number of characters from the right side of a text string.

Syntax:

=RIGHT(text, num_chars)

Parameters:

• text (required): The text string from which you want to extract characters. This can be a direct text string or a reference to a cell containing the text.
• num_chars (required): The number of characters you want to extract from the right side of the text string.

Key Points:

• The RIGHT function extracts characters based on character length, not byte length.
• It is suitable for text in languages that use single-byte characters (such as English, Spanish, etc.).
• If num_chars is greater than the length of the text string, it returns the entire string.

Example Usage:

1. Extracting the Last 3 Characters

If cell A1 contains the text “Excel”, the formula:

=RIGHT(A1, 3)

This will return:

cel

Explanation:

• The function extracts the last 3 characters from the right side of the word “Excel,” resulting in “cel”.

2. Extracting All Characters (When num_chars > Text Length)

If cell B1 contains “Data” and you specify num_chars as 10:

=RIGHT(B1, 10)

This will return:

Data

Explanation:

• Since the num_chars exceeds the length of the text, it returns the entire string, “Data”.

2. RIGHTB Function

The RIGHTB function works similarly to RIGHT, but it counts bytes instead of characters. This is particularly important for languages that use double-byte characters (like Japanese or Chinese), where each character may take up more than one byte.

Syntax:

=RIGHTB(text, num_bytes)

Parameters:

• text (required): The text string from which you want to extract bytes. This can be a direct text string or a reference to a cell.
• num_bytes (required): The number of bytes you want to extract from the right side of the text string.

Key Points:

• The RIGHTB function extracts based on byte length rather than character length, making it suitable for double-byte character sets (DBCS).
• If num_bytes exceeds the total byte length of the string, it will return the entire string.
• In languages with single-byte characters (like English), RIGHTB works similarly to RIGHT, but it counts bytes instead of characters, so be mindful of text encoding.

Example Usage:

1. Extracting the Last 6 Bytes (in a DBCS Language)

If cell C1 contains the text “こんにちは” (which uses 10 bytes in UTF-8 encoding), and you want to extract the last 6 bytes, the formula:

=RIGHTB(C1, 6)

This will return:

にちは

Explanation:

• The RIGHTB function extracts the last 6 bytes of the string. In this case, “にちは” consists of 6 bytes (3 characters).

2. Extracting All Bytes

If cell D1 contains “ABCDEF” and you want to extract the last 8 bytes:

=RIGHTB(D1, 8)

This will return:

ABCDEF

Explanation:

• Since the string “ABCDEF” has 6 bytes (each character is a single byte in English), the function returns the entire string, “ABCDEF”.

Key Differences Between RIGHT and RIGHTB:

• Character vs Byte:
  • RIGHT: Extracts based on character count (useful for single-byte character sets).
  • RIGHTB: Extracts based on byte count, which is essential for double-byte characters in languages like Chinese, Japanese, or Korean.
• Byte Handling:
  • In RIGHT, one character equals one byte.
  • In RIGHTB, characters in double-byte sets (like those in East Asian languages) take up more than one byte.

Example Comparison:

If cell E1 contains the Japanese text “こんにちは” (10 bytes in total), applying RIGHT and RIGHTB:

1. Using RIGHT to extract the last 3 characters:

=RIGHT(E1, 3)

This will return:

にちは

Explanation:

• It counts characters, so it returns the last 3 characters “にち” (since “こんにちは” has 5 characters).

2. Using RIGHTB to extract the last 6 bytes:

=RIGHTB(E1, 6)

This will return:

にちは

Explanation:

• It counts bytes, and “にちは” is 6 bytes, so it extracts the last 6 bytes of the string.

Notes:

• Non-Double-Byte Languages: If you are working with text in languages like English, RIGHTB behaves the same as RIGHT because each character is represented by a single byte.
• Character Encoding: For languages that use multi-byte character encoding (e.g., Japanese, Chinese), RIGHTB is critical to handle the byte count properly, as some characters take up more than one byte.
• Zero or Negative num_chars/num_bytes: If num_chars or num_bytes is 0 or negative, Excel returns an empty string.

Related Functions:

• LEFT / LEFTB: Extract characters or bytes from the left side of a text string.
• MID / MIDB: Extract characters or bytes from the middle of a text string.
• LEN / LENB: Return the number of characters or bytes in a text string.
• TRIM: Remove extra spaces from a text string, useful for cleaning up data before using RIGHT or RIGHTB.

The RIGHT and RIGHTB functions are essential tools in Excel for manipulating text strings, especially when dealing with variable text lengths or specific encoding requirements.