Mastering String Manipulation in Excel: The RIGHT Function

In this video, we delve into the RIGHT function in Excel, a powerful tool for string manipulation. The RIGHT function allows you to extract a specific number of characters from the end of a string, making it incredibly useful for tasks like pulling last names from full names. Throughout the video, we cover the syntax, practical applications, and demonstrate its use through examples.

What will you learn

• Understanding the purpose and functionality of the RIGHT function in Excel.
• How to write the RIGHT function with correct syntax.
• Practical applications of the RIGHT function in real-world scenarios.
• String manipulation techniques using LEFT and RIGHT functions for data cleaning.
• The differences and similarities between the LEFT and RIGHT functions.
• How to handle common data extraction tasks using string functions.

Takeaway notes

• The RIGHT function is used to extract a specified number of characters from the end of a text string.
• The syntax for the RIGHT function is: RIGHT(text, number_of_characters).
• It is particularly useful for extracting last names or any fixed-length data from the right side of a string.
• Combining LEFT and RIGHT functions can significantly simplify data cleaning and preparation processes.
• These functions are handy when dealing with data glitches, especially from legacy databases.
• The RIGHT function helps in efficient string manipulation and index creation in Excel.

Practice questions

1. Basic Extraction: Write a formula using the RIGHT function to extract the last four characters from the string "Southampton".
2. Fixed-length Data: Given the text "Employee1234", use the RIGHT function to extract the last four characters.
3. Data Cleaning: You have a list of full names in column A (First Last). Write a formula to extract only the last names using the RIGHT function.
4. Combined Use: How would you use LEFT and RIGHT functions together to extract the middle name from the full name "John Michael Doe"?
5. Real-World Application: Extract the file extension from the filename "document.pdf" using the RIGHT function.
6. Dynamic Length Extraction: A list of product codes are in column B in the format "Prod-XYZ123". Extract the numeric part of the code using RIGHT.
7. Error Handling: What will the RIGHT function return if used with a negative number for the number_of_characters argument?
8. Variable Length Data: Using a combination of LEN and RIGHT functions, write a formula to extract all characters from the fourth character to the end in "Schedule2022".
9. Database Management: Extract the last three digits of each cell in a dataset containing account numbers in column C.
10. String Comparisons: Compare the output of RIGHT("Information Technology", 12) with RIGHT("Information Systems", 12) and explain the differences.
11. Legacy Data Extraction: Write a RIGHT function formula to extract a date suffix from the format "Report_2023-Mar".
12. Text to Columns: Split the text "FirstName_LastName" into two separate cells using RIGHT in conjunction with other functions.
13. Data Consistency: Given inconsistent data lengths in a column, how would you ensure consistent extraction of the rightmost part using the RIGHT function?
14. Conditional Extraction: Using IF and RIGHT functions, write a formula that extracts the last character only if it is a number from the string in cell A2.
15. Parsing Alphanumeric Data: Given a list of strings where each ends with a three-letter country code, e.g., "Product_ABC", extract the country code using the RIGHT function.