How to detect the length of a string with Microsoft Lists formatting

By João Ferreira Jan 4, 2024 Formatting 0 Comments

One of the things that I like to do at the beginning of every year is to review some of my Microsoft Lists and create new views or refine some of the existing ones. This year was no exception, and while I was working on one of my lists, I decided to fix a problem (more about it here) that had been bothering me for a long time but in the process, I found another annoying issue.

My goal was to split a string and I jumped immediately into the length function; however this function only measures the size of an array, not the length of a string. This means that if you want to apply some conditional formatting based on the number of characters in a text field, or if you want to split the string in half you are out of luck.

So, how can we solve the length issue?

Well, after trying a few hacks and tricks, I finally found a solution that works. And guess what? I was not the only one who had this problem. In fact, Chris Kent, a Microsoft MVP and a guru of Microsoft Lists formatting, had already talked about the same solution a while ago. I wish I had googled it first!

The trick to detect the length of a string is to append a unique character that you are certain does not appear in the field that you are formatting. Then, you can use the indexOf function to find the position of that character. The result will be the length of your string. Here is an example:

"txtContent": "=indexOf(@currentField+'@','@')"

To better understand the differences and the results, check the following formattings and outputs:

• Checking the length of Hello World using the indexOf method described above, the output is 11

  "txtContent": "=indexOf('Hello World'+'@','@')"

• Checking the length of Hello World using the length method, the output is 1

  "txtContent": "=length('Hello World')"

• Checking the length of an empty field, the output is 0

  "txtContent": "=length(@currentField)"

• Checking the length of a person field with 2 users, the result is 2

  "txtContent": "=length(@currentField)"

I hope you find it useful and that it helps you create more awesome views for your lists.