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.
It follows a simple pattern.
- Number of characters that we want (optional)
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 “,”).
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 “Left 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 “Left Function” in a formula, you should review it and make everything more straightforward.
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
2 thoughts on “SharePoint: Left Function”
Hi thanks for the article. Can I use the LEFT function inside Column formatting JSON style? I would like to format column based on first letter of Title. E.g. If the letter == ‘T’ make it green. But however I try to use the Left function with @currentField it just doesnt react. Thanks a lot!
Sorry for taking a bit to reply, but I wanted to check if I could do something, and I think I have a solution for you :).
You can use the “indexOf” function, and if it’s equal to 0, it starts with that character or string. For example:
"class": "=if(indexOf(@currentField,'test') == 0,'sp-field-severity--warning', '')"
You can change the formula to have a single character or a string, and it will work.
Can you please try and let me know if it works?