by “Len function” will count the number of provided characters in the parameter. It will count them all, including spaces.
Usage
To use it, it’s quite simple. Just pass the text string you want to count, and it will return the number of characters.
=LEN("Manuel Gomes")
Will return 12, but.
=LEN("Manuel Gomes ")
Will return 15.
Limitations
Although the reference indicates that we should provide a Text string, we can pass other types, and the formula will not return errors. Let’s take a look at some examples:
Number
The count of the digits, including the decimal places. If you define the decimal places, it will count trailing zeros also. The following examples all will return 3.
3.10 (fixed decimal places to 2)
123 (no decimal places)
3.12 (automatic decimal places)
Date
It will usually return 5. Why 5? Dates are stored internally as sequential serial numbers to enable calculations starting from December 31, 1899 (serial number 1). Since SharePoint can convert it into a string automatically, you’ll get five since that’s the number of days elapsed since the first serial number. You can get the number quickly by running the following formula:
=DATEDIF("1900-01-01",TODAY(),"D") + 2
We need to add two because we need to count today’s day and, since it’s not complete, will not be included. Also, we cannot define in the DateDif the token one date of “December 31, 1899” because SharePoint will return an error, so we need to start on the next day.
Yes/No
It will return 4 for “Yes” and 5 for ”No.” I would expect three and two, respectively, or 1 and 0, but they are stored internally differently.
Location
It isn’t supported, but you can apply the function to each of its values.
Person
Since the person is an ID, it will return the count of characters contained in the ID. Usually 16.
Recommendations:
- Although you can use it with other types, keep its usage for text fields.
Sources:
Microsoft’s Len Function Reference
Introduction to SharePoint formulas and functions
Back to the SharePoint’s list formula reference.
Featured Image by Charles 🇵🇭 on Unsplash
