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

Manuel Gomes

I have 18 years of experience in automation, project management, and development. In addition to that, I have been writing for this website for over 3 years now, providing readers with valuable insights and information. I hope my expertise allows me to create compelling, informative content that resonates with the audience.

View all posts by Manuel Gomes →

2 thoughts on “SharePoint: Left Function

  1. 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!

    1. Hi,
      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:

      "attributes": {
      "class": "=if(indexOf(@currentField,'test') == 0,'sp-field-severity--warning', '')"
      }

      Like this:
      if statement to format the column if it starts with a valid string

      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?

      Manuel

Leave a Reply

Your email address will not be published. Required fields are marked *