Getting SharePoint to use multi-line text fields for calculated fields

Requirement: Trim, truncate, or otherwise modify the text from one SharePoint list field to make it appear in another.
Solution: Make the new field a calculated field and enter the formula, referencing the original field in [brackets].
Obstacle: Text functions in SharePoint list columns do not work properly on multiple line text columns.

The work-around here is to trick the new SharePoint list field into thinking it is operating on a single-line text field. While it is true that the text functions don’t work perfectly for multiple line text fields, they are adequate for simple tasks such as truncation. The issue is that SharePoint validates the calculated field when saving and throws an error if the function refers to a multiple line text field, preventing the save operation.

So how do you trick it?
1. Create Field1, single line text
2. Create Field2, calculated field, formula=RIGHT([Field1],LEN([Field1])-8)
    (this gets rid of the html tag that will appear at the beginning as an artifact of operating on a multiple line text field)
3. Create Field3, calculated field, formula=LEFT([Field2],50) & “…”
    (this will display the first 50 characters of the original field follows by the ellipse)
4. To prevent validation, do this from the site settings window
     a. Delete Field1
     b. Create Field1, multi-line text
5. Create a custom view and hide Field1 and Field2 and set it as the default view
     (they will still appear in the item view, but not in the edit window since they are calculated fields)

There you have it. Steps for tricking SharePoint into doing something it was designed to do, but prevented from by poor validation logic.  Now how to we trick those MS Devs into removing that erroneous validation logic?

Enjoy.


Did this help you?  If so, please leave a comment!