“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:

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

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

%d bloggers like this: