Sharepoint Calculated Column With Multiple Lines Of Text

Sep 25, 2015 The only type of column that will accept a URL (i.e. A string of text) with more than 255 characters is a Multiple Lines of Text column. But it wasn’t feasible to expect the users to create the hyperlink themselves by adding anchor text and hyperlinking it. SharePoint Calculated Column Formula, and; SharePoint Column Validation. Actually, it’s by design, there are some fields that unsupported to use in calculated column like. ID, (Check how to use ID Field in Calc ulated Column in SharePoint). Multiple lines of text; Choice field with multiple selections.

I recently set up a custom list, and needed to create a column for users to paste links to specific document library folders within their site. I quickly realised that the Hyperlink column wasn’t going to work, because it has a character limit of 255. And, these document library folder URLs were going to be longer than 255 characters.

This is the error message you get when a URL is longer than 255 characters

As we all know SharePoint creates long URLs for folders and files in document libraries. And, the deeper the folder structure, the longer the address. This particular team had set up their document libraries with quite deep folder structures, resulting in the long URLs. (This one good reason to not use folders in SharePoint document libraries.)

The other issue I had was that the input of the URL needed to be as simple as possible for the users. I couldn’t ask them to do anything more than go to the library, copy the address, and then paste it into the field on the custom list. Even that was a bit of a stretch for some 😉

The only type of column that will accept a URL (i.e. a string of text) with more than 255 characters is a Multiple Lines of Text column. But it wasn’t feasible to expect the users to create the hyperlink themselves by adding anchor text and hyperlinking it. Nor did I want the full URL to display on the list view, as it would be too long and unattractive looking.

So, I thought I might be able to find a way to use a Multiple Lines of Text column, and then use a Calculated column to display it as a neat, short hyperlink. The only problem I needed to solve was the one of Calculated columns not allowing Multiple Lines of Text columns to be used in their formulas.

After much thinking and experimenting and googling, I came up with a solution. It’s not the simplest, but it’s quite fun in its wackiness.

I can’t profess that this is all my own solution – it’s based on bits and pieces I found on the internet – particularly a post from Nothing But SharePoint: Taming the Elusive “Calculated Column” – Referencing Multiple Lines of Text Column.

The solution

In my custom list, I created a new column named ‘File Location’ and made it a Single Line of Text type.

I then created a Calculated column named ‘Location’. I added this formula:

=CONCATENATE('<a href=',[File Location],'>View</a>')

This tells the column to create a hyperlink, using the value of the File Location column, and wrap it around the word ‘View’ (thus providing the neater, short link). Thanks to my manager for working out the exact structure of this formula.

A detailed explanation of the different bits of the formula (for non-techies like me)

I also set the Calculated column to return the data type ‘number‘ (rather than the default ‘Single line of text’). This ensures the text will display as html.

Sharepoint Calculated Column With Multiple Lines Of Text In A Cell Excel Mac

I then deleted the original ‘File Location’ column.

Next, I created a new column, also named ‘File Location’ (It MUST be the same name as the original column) and made it a Multiple Lines of Text type, with plain text. This will only work if the column is plain text, not rich text.


Torrent search engine windows 10. For added measure, we have also included some terrific free torrent clients that support Android, because you never know when people want to move this storage to their phones. You can actually download torrents from the client, or automatically download the RSS feeds as well, making this torrent client one of the best.It is completely secure, and the best part is that all torrents are downloaded from the servers of Bitport.io. For those who do not know, this client is based on cloud, and one of the best things is that you can even use apps to this that are available on Android, iOS, App TV, Roku, as well as integration to Chromecast.

In the meantime, the ‘Location’ Calculated column continued to refer to a column named ‘File Location’. I just tricked it into referring to a Multiple Lines of Text column, rather than a Single Line of Text column.

Now, when a user pastes a long URL into the ‘File Location’ column, the ‘Location’ Calculated column converts it to a nice hyperlink to be displayed on the list view.

Note: It’s important to get this all set up correctly the first time. If you need to go in and make any changes to the Calculated column and then save it again, it will error – resulting in you needing to start the whole set up all over again.

I stumbled upon this by accident. Try to do this by adding a multiple line of text field to a calculated column in SharePoint and it will tell you that it cannot be done. But it can with the use of InfoPath and some trickery:

For this example I will call the field that will be the multiple line of text field “Holder”.

1. Create a single line of text field and name it what you will be naming your multiple line of text field.

2. Create you calculated column and add the field you just create to it.

3. Open the form in InfoPath.

4. Delete the Single Line of Text field you just create and the publish.

Sharepoint calculated column with multiple lines of text format

5. In InfoPath create a Multiple Line of Plain Text field and name it EXACTLY the same as your old Single Line of Text field and publish once more.

Sharepoint Calculated Column Multiple Lines Of Text

You are now done and whatever you put in your multiple line of text field will show up in your Calculated Column.