Recently, I worked on a SharePoint project where I needed to calculate the length of a SharePoint list column. We can achieve this using a SharePoint calculated column.
Basically, the LEN() function in SharePoint can be used to calculate the length of a string. This is useful when you want to get the total number of characters in a specified string.
Syntax:
= LEN([Column Name])Parameters:
- Column Name – Take the text string column for which you want to find the length of characters.
Output Returned:
- It will return the integer that represents the count of characters for a specific string.
Additional Note:
- The LEN() function will also count spaces as characters. So, if a text string consists of spaces at the beginning or end of a string, or spaces in between words, these were also considered as one character.
Limitations:
Though it indicates that we should provide a text string, LEN() will not generate an error for other types.
- If we apply LEN () for the Yes/ No column, it will return the output as 4 for “Yes” and 5 for ”No.” But, it should have to be 3 for “Yes” and 2 for “No”. This is because [Yes/No] will be stored as [True/False] internally.
- It does not support the Location column, but it can apply to each value.
Check out SharePoint Calculated Column Date Examples
SharePoint Calculated Column LEN() Examples
Below, you can see a couple of examples related to the LEN() function in a SharePoint calculated column.
Example 1: Get the Length of a SharePoint List Column in a Calculated Column
Suppose you have a SharePoint list “Event Registrations List” with the following columns in a table. And, you want to quickly know the total number of characters in the list column, i.e, for example: Event Name.
| Column Name | Column Type |
|---|---|
| Event Name | Title – Renamed to Title column |
| Event Date | Date and Time column |
You can use the following function:
=LEN([Event Name])Here are the steps to create a calculated column in a SharePoint list:
- Open your SharePoint list/ library, where you want to add the calculated column. Click on + Add column. Select See all column types. Then, click the Next button.
- Next, it will redirect to the Create Column page, where you can create or add a column to your SharePoint list.
- On the Create Column page, provide a name for a column and choose the data type as Calculated (calculation based on other columns).
- Enter the following formula in the Formula box. Choose the data type returned from this formula to ” Number“. Click on OK at the bottom of the page.
=LEN([Event Name])
- Now, return to your SharePoint list to view the newly created calculated column. It will display the length of a string [Event Name]. You can have a look at the reference image:
Check out SharePoint Calculated Column Round Up to Decimal Places
Example 2: SharePoint Calculated Column – Check Length of a String
For this example, I will use the same SharePoint list as in Example 1.
In the SharePoint list “Event Registration,” I wanted to check the length of a string from a SharePoint list column [Event Name].
Which means that the Event name should be below 50 characters. If it is greater than 50 characters, it should display “Invalid;” otherwise, it should display “Valid.”
Here are the steps to follow:
Follow the steps below:
- On the Create Column window, provide a name for the column and select the type as Calculated (calculation based on other columns).
- Copy and paste the below given formula in the Formula box. Select the data type returned from this formula to “Single line of text”. Click on the OK button.
=IF(LEN([Event Name])<50,"Valid","Invalid")Replace the [Event Name] with your column name from the Insert box.
- Now, the calculated column has been created.
- Open your SharePoint list. The calculated column will display the output as Invalid and Valid. Have a look at the image below:
SharePoint Calculated Column LEN() Function – Video Tutorial
In this tutorial, I explained how to use the LEN() function in a SharePoint calculated column with two examples.
You may also like:
Preeti Sahu is an expert in Power Apps and has over six years of experience working with SharePoint Online and the Power Platform. She is the co-author of Microsoft Power Platform: A Deep Dive book. As a Power Platform developer, she has worked on developing various tools using Power Apps and Power Automate. She also makes Microsoft 365 videos and shares them on YouTube.
