SharePoint: Left Function

The “Left function” is part of a group of functions that help us do text manipulation. The “left” gives us a hint into what it does. It starts from the left of the string 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'

=LEFT(Title,6)

will return 

'Manuel'

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 first one:

String
'Manuel T. Gomes'

=LEFT(Title)

will return 

'M'

What about dates?

=Left([Added Date],3)

will return

442

(For your reference, I’m writing this article on the 11th March 2021)

Why 442? 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.

=Left([Cost],3) 

If the value is 55.5 it will return

55.

If the value is 555.5 it will return 

555

The behavior is similar to a date. The value is converted to a string, and then the “left 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 “Left 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 “Left 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 “LEFT” 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 LEFT Function Reference

Introduction to SharePoint formulas and functions

Back to the SharePoint’s list formula reference.

Photo by David Marcos on Unsplash