RIGHT Function in Excel

Part 1: Introduction

💡 Definition: The RIGHT function in Microsoft Excel extracts a specified number of characters from a text string’s end (rightmost) side. It allows you to retrieve a portion of text based on the desired length.

🎯 Purpose: The RIGHT function is useful when extracting a specific number of characters from the right side of a text string, such as when you want to retrieve file extensions, last names, or the last part of a longer string.

📜 Syntax & Arguments:

syntax
`RIGHT(text, num_chars) `

🔍 Explain the Arguments:

• `text`: The text string from which you want to extract characters.
• `num_chars`: The number of characters you want to extract from the right side of the text string.

📤 Return value: The RIGHT function returns the number of rightmost characters from the text string.

💬 Remarks:

• The `num_chars` argument must be a positive number or a reference to a cell containing a positive number.
• If the specified `num_chars` is greater than the length of the text string, the entire text string will be returned.
• The RIGHT function is case-sensitive, considering uppercase and lowercase characters differently.

Now let’s explore some examples to understand how to use the RIGHT function effectively.

Part 2: Examples

✨ Example 1:

AB
1File Name
2document.docx
4presentation. pptx

📝 Formula: In cell B2, enter `=RIGHT(A2, 4)`

Explanation: In this example, we have file names in column A and want to extract the file extensions from each word. By using the RIGHT function with the `num_chars` the argument set to 4, we can retrieve the rightmost four characters from each word, representing the file extension. The result in cell B2 will be “docx”.

✨ Example 2:

AB
1Sentence
2Hello World
3Excel Rocks
4Coding Fun

📝 Formula: In cell B2, enter `=RIGHT(A2, 5)`

Explanation: In this example, we have sentences in column A and want to extract the last five characters from each sentence. Using the RIGHT function with the `num_chars` argument set to 5, we can retrieve the rightmost five characters from each sentence. The result in cell B2 will be “World”.

✨ Example 3:

📝 Formula: In cell B2, enter `=RIGHT(A2, 3)`

Explanation: In this example, we have URLs in column A, and we want to extract the top-level domain (TLD) from each URL. By using the RIGHT function with the `num_chars` argument set to 3, we can retrieve the rightmost three characters from each URL, representing the TLD. The result in cell B2 will be “com”.

✨ Example 4:

AB
1Full Name
2John Smith
3Jane Doe
4Michael Lee

📝 Formula: In cell B2, enter `=RIGHT(A2, FIND(" ", A2)-1)`

Explanation: In this example, we have full names in column A and want to extract the last word from each word. By using the RIGHT function with the `num_chars` the argument set to the position of the space character (found using the FIND function) minus one, we can retrieve the rightmost characters after the space. The result in cell B2 will be “Smith”.

✨ Example 5:

AB
1Sentence
2Hello World!
3Have a nice day!
4Excel is powerful!

📝 Formula: In cell B2, enter `=RIGHT(A2, LEN(A2)-FIND(" ", A2))`

Explanation: In this example, we have sentences in column A and want to extract the last word from each sentence. By using the RIGHT function with the `num_chars` argument set to the length of the sentence minus the position of the space character (found using the FIND function), we can retrieve the rightmost characters after the space. The result in cell B2 will be “World!”.

✨ Example 6:

AB
1Product
2Laptop
3Smartphone

📝 Formula: In cell B2, enter `=RIGHT(A2, 3)`

Explanation: In this example, we have product names in column A and want to extract the last three characters from each word. By using the RIGHT function with the `num_chars` argument set to 3, we can retrieve the rightmost three characters from each word. The result in cell B2 will be “top”.

✨ Example 7:

AB
1Text
2Welcome!
3Congratulations!
4Amazing!

📝 Formula: In cell B2, enter `=RIGHT(A2, LEN(A2)-1)`

Explanation: In this example, we have sentences in column A and want to remove the exclamation mark from each sentence. By using the RIGHT function with the `num_chars` argument set to the length of the sentence minus one, we can retrieve the rightmost characters after excluding the exclamation mark. The result in cell B2 will be “Welcome”.

✨ Example 8:

AB
1Filename
2document_v1
4presentation_v3

📝 Formula: In cell B2, enter `=RIGHT(A2, LEN(A2)-LEN("_v1"))`

Explanation: In this example, we have filenames in column A, and we want to remove the version number (e.g., “_v1”, “_v2”) from each filename. By using the RIGHT function with the `num_chars` argument set to the length of the filename minus the length of the version number (in this case, “_v1”), we can retrieve the rightmost characters after excluding the version number. The result in cell B2 will be a “document”.

✨ Example 9:

AB
1Time
212:30 PM
309:45 AM
406:15 PM

📝 Formula: In cell B2, enter `=RIGHT(A2, 2)`

Explanation: In this example, we have time values in column A in the format “hh:mm AM/PM”, and we want to extract the AM/PM part. Using the RIGHT function with the `num_chars` argument set to 2, we can retrieve the rightmost two characters, representing the AM/PM indicator. The result in cell B2 will be “PM”.

Part 3: Tips and Tricks

1️⃣ Use the RIGHT function to extract a specific number of characters from the right side of a text string.
2️⃣ Experiment with different combinations of functions like FIND, SEARCH, and LEN to determine the position or length required for the `num_chars` argument.
3️⃣ If you need to extract a fixed number of characters from multiple text strings, use cell references for both the text and `num_chars` arguments to easily apply the RIGHT function across a range of cells.
4️⃣ If you want to remove specific characters from the left side of a text string, consider using the LEFT function instead.
5️⃣ Remember that the RIGHT function is case-sensitive so that it will consider uppercase and lowercase characters as different.
6️⃣ Be cautious when the `num_chars` argument exceeds the length of the text string, as it will return the entire text string. Ensure your formula accounts for such scenarios.
7️⃣ Remember that the RIGHT function works only with text strings so it won’t work directly with numbers or dates. Convert them to text before using the RIGHT function if necessary. 8️⃣ Consider combining the RIGHT function with other functions like IF or LEN to create dynamic formulas based on specific conditions or variable lengths.
9️⃣ Practice using the RIGHT function with different examples to enhance your familiarity and proficiency in manipulating text strings.

By following these tips and utilizing the RIGHT function in various scenarios, you can efficiently extract desired portions of text strings from the right side of Microsoft Excel.