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.

Usage

It follows a simple pattern.

  1. String
  2. Number of characters that we want (optional)

Example:

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 “,”).

Limitations

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.

Recommendations:

  1. 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.
  2. 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.”
  3. 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.
Please note that formulas may have localization-based differences. For example, you should write “RIGHT” with a comma separating each of the arguments, but if your regional settings are set to Portugal, you should use “;” instead.[/su_shadow]

Sources:

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

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: