The “Right function” is part of a group of functions that help us do text manipulation. The “right” gives us a hint into what it does. It starts from the string’s right and will return the number of characters specified in the second parameter. It may look that it is not that useful but let me show you how you can use it.
It follows a simple pattern.
- Number of characters that we want (optional)
String 'Manuel T. Gomes' =RIGHT(Title,6) will return 'Gomes'
If the number of characters is inferior to the string’s length, SharePoint won’t return an error. It will return all characters available—so no need to check for errors.
If you don’t define the number of characters, you’ll get the last one:
String 'Manuel T. Gomes' =RIGHT(Title) will return 's'
What about dates?
=RIGHT([Added Date],3) will return 266
(For your reference, I’m writing this article on the 11th March 2021)
Why 266? SharePoint treats dates much like Excel does. It stores it internally as a number (here’s a tutorial on converting it back to date). The full number would be “44266,” so SharePoint converts the number into a string and returns the first 3 characters.
You will have the same behavior with a field of type Number, but let’s try one with decimal places.
=RIGHT([Cost],3) If the value is 55.555 it will return 555 If the value is 555.5 it will return 5.5
The behavior is similar to a date. The value is converted to a string, and then the “right function” will return the first X characters. In the case above, the separator (in some locales can be a “,”).
Don’t use negative numbers as the second parameter. The function will not fail, but it will return an error. If you’re using that value elsewhere, you’ll be propagating an error or, worse; you’ll have things break.
- Use it in text columns unless you have some use case to get the information from numbers or dates. It’s not common, but it may happen.
- Be careful with the second parameter. If you’re calculating it, it may return an error or a negative number. You always want to control the value, so check it before using the “Right function.”
- Don’t nest it. There’s no real reason to do it, but if you find yourself in a situation where you have nested multiple “Right Function” in a formula, you should review it and make everything more straightforward.
Microsoft’s RIGHT Function Reference
Introduction to SharePoint formulas and functions
Back to the SharePoint’s list formula reference.
Photo by Giulia May on Unsplash