✨ Part 1: Introduce
🔹 Definition: The RIGHTB function is a text function in Microsoft Excel that returns a specified number of characters from the rightmost side of a text string, considering double-byte characters.
🔹 Purpose: The RIGHTB function aims to extract a specific number of characters from the end of a text string, mainly when dealing with languages that use double-byte characters.
🔹 Syntax & Arguments: The syntax of the RIGHTB function is as follows:
RIGHTB(text, num_chars) text: The text string from which you want to extract characters.num_chars: The number of characters to extract from the right side of the text string.
🔹 Explanation of Arguments:
text: This argument represents the text string or cell reference containing the text from which you want to extract characters.num_chars: This argument specifies the number of characters you want to extract from the right side of the text string.
🔹 Return Value: The RIGHTB function returns a text string containing the specified number of characters from the right side of the input text string.
🔹 Remarks:
- The RIGHTB function is handy when working with languages that use double-byte characters, such as Chinese, Japanese, or Korean.
- If the
num_charsthe argument is greater than the length of the text string. The function will return the entire text string.
✨ Part 2: Examples
Example 4: Extracting the Last 4 Digits of Phone Numbers Suppose you have a list of phone numbers in column A and want to extract the last four digits of each number. Here’s how you can do it:
| A | B | |
|---|---|---|
| 1 | Phone Number | Last Four Digits |
| 2 | 1234567890 | =RIGHTB(A2, 4) |
| 3 | 9876543210 | |
| 4 | 5678901234 |
- In cell B2, use the formula
=RIGHTB(A2, 4)to extract the last four digits of the phone number. - The result in cell B2 will be “7890” for the phone number “1234567890”.
Example 5: Extracting Rightmost Character from Text Suppose you have a list of codes in column A and want to extract the rightmost character from each code. Here’s how you can do it:
| A | B | |
|---|---|---|
| 1 | Code | Last Character |
| 2 | ABCD | =RIGHTB(A2, 1) |
| 3 | XYZ | |
| 4 | PQR |
- In cell B2, use the formula
=RIGHTB(A2, 1)to extract the rightmost character from the code. - The result in cell B2 will be “D” for the code “ABCD”.
Example 6: Extracting the Last Word from Text Suppose you have a list of sentences in column A and want to extract the last word from each sentence. Here’s how you can do it:
| A | B | |
|---|---|---|
| 1 | Sentence | Last Word |
| 2 | I love Excel spreadsheets | =RIGHTB(A2, FIND(“@”, SUBSTITUTE(A2, ” “, “@”, LEN(A2) – LEN(SUBSTITUTE(A2, ” “, “”))))) |
| 3 | This is a sample sentence. | |
| 4 | Extracting the last word. |
- In cell B2, use the formula
=RIGHTB(A2, FIND("@", SUBSTITUTE(A2, " ", "@", LEN(A2) - LEN(SUBSTITUTE(A2, " ", "")))))to extract the last word from the sentence. - The formula uses the SUBSTITUTE function to replace all spaces except the last room with the “@” symbol. Then, it uses the FIND function to locate the position of the “@” symbol and extracts the text from that position onwards.
- The result in cell B2 will be “spreadsheets” for the sentence “I love Excel spreadsheets”.
Example 7: Extracting Last Character from Concatenated Text Suppose you have a table with two columns. Column A contains first names, and Column B contains last names. You want to create a new column that displays the last character of each person’s full name. Here’s how you can do it:
| A | B | C | |
|---|---|---|---|
| 1 | First Name | Last Name | Last Character |
| 2 | John | Smith | =RIGHTB(CONCATENATE(A2, ” “, B2), 1) |
| 3 | Jane | Doe | |
| 4 | Tom | Johnson |
- In cell C2, use the formula
=RIGHTB(CONCATENATE(A2, " ", B2), 1)to concatenate the first and last names and then extract the last character. - The result in cell C2 will be “h” for the name “John Smith”.
Example 8: Extracting Rightmost Digits from Alphanumeric Codes Suppose you have a list of alphanumeric codes in Column A and want to extract the rightmost digits from each code. Here’s how you can do it:
| A | B | |
|---|---|---|
| 1 | Alphanumeric Code | Rightmost Digits |
| 2 | AB12C3 | =RIGHTB(SUBSTITUTE(A2, CHAR(32), “”), LEN(A2) – MAX(IF(ISNUMBER(1*MID(A2, ROW(INDIRECT(“1:” & LEN(A2))), 1)), ROW(INDIRECT(“1:” & LEN(A2)))) – LEN(A2) + 1, LEN(A2))) |
| 3 | XY34Z | |
| 4 | PQR567 |
- In cell B2, use the formula
=RIGHTB(SUBSTITUTE(A2, CHAR(32), ""), LEN(A2) - MAX(IF(ISNUMBER(1*MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), ROW(INDIRECT("1:" & LEN(A2)))) - LEN(A2) + 1, LEN(A2)))to extract the rightmost digits from the alphanumeric code. - The formula uses the SUBSTITUTE function to remove any spaces from the code and then combines MAX, IF, ISNUMBER, MID, ROW, and LEN functions to find the position of the rightmost digits.
- The result in cell B2 will be “123” for the code “AB12C3”.
Example 9: Extracting Rightmost Word from Text Suppose you have a list of sentences in Column A and want to extract the rightmost word from each sentence. Here’s how you can do it:
| A | B | |
|---|---|---|
| 1 | Sentence | Rightmost Word |
| 2 | I love using Microsoft Excel for data analysis. | =RIGHTB(A2, LEN(A2) – FIND(“@”, SUBSTITUTE(A2, ” “, “@”, LEN(A2) – LEN(SUBSTITUTE(A2, ” “, “”))))) |
| 3 | This is a sample sentence for demonstration. | |
| 4 | Extracting the rightmost word can be useful. |
- In cell B2, use the formula
=RIGHTB(A2, LEN(A2) - FIND("@", SUBSTITUTE(A2, " ", "@", LEN(A2) - LEN(SUBSTITUTE(A2, " ", "")))))to extract the rightmost word from the sentence. - The formula uses the SUBSTITUTE function to replace the last space with the “@” symbol and then uses the FIND function to locate the position of the “@” symbol and extract the text from that position onwards.
- The result in cell B2 will be “analysis” for the sentence “I love using Microsoft Excel for data analysis.”
Example 10: Extracting Rightmost Non-Numeric Characters from Text Suppose you have a list of text strings in Column A and want to extract the non-numeric characters from each string. Here’s how you can do it:
| A | B | |
|---|---|---|
| 1 | Text | Rightmost Non-Numeric |
| 2 | ABC1234XYZ | =RIGHTB(A2, LEN(A2) – MAX(IF(ISNUMBER(1*MID(A2, ROW(INDIRECT(“1:” & LEN(A2))), 1)), ROW(INDIRECT(“1:” & LEN(A2)))) – LEN(A2) + 1, LEN(A2))) |
| 3 | PQR56789 | |
| 4 | 123ABCXYZ |
- In cell B2, use the formula
=RIGHTB(A2, LEN(A2) - MAX(IF(ISNUMBER(1*MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), ROW(INDIRECT("1:" & LEN(A2)))) - LEN(A2) + 1, LEN(A2)))to extract the rightmost non-numeric characters from the text. - The formula uses a combination of MAX, IF, ISNUMBER, MID, ROW, and LEN functions to find the position of the rightmost non-numeric character.
- The result in cell B2 will be “XYZ” for the text “ABC1234XYZ”.
Feel free to apply these examples to your Excel spreadsheets and modify them per your specific requirements.
✨ Part 3: Tips and Tricks
- The RIGHTB function is handy when dealing with languages that use double-byte characters, as it correctly handles the extraction of characters in those languages.
- Remember to adjust the
num_charsthe argument is based on the specific number of characters you want to extract from the right side of the text string. - If you need to combine the result of the RIGHTB function with other text or formulas, you can use concatenation operators like “&” or the CONCATENATE function.
These examples demonstrate using the RIGHTB function in various business scenarios to extract specific information or manipulate text strings. Feel free to apply these techniques to your Excel and adapt them to your needs.