How to trick a Calculated column into referring to a Multiple Lines of Text column
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.
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.
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.
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.
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.
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.