SharePoint 2010 calculated column and hyperlink (no workflows or scripts needed)

Often you will get a requirement like this:

Create a calculated column which concatenate the url text with an existing column value.

This seems to be an easy task, except one thing: SharePoint doest not render hyperlink in calculated column by default.

For example, I have a list with 2 columns: Search term and Google Search. Google Search is a calculated column with this formula

Now, this is what I will get by default:

There are a few ways to fix this problem. Usually people will recommend you to create a Hyperlink column instead and create a workflow to update the Hyperlink value (http://social.technet.microsoft.com/Forums/ar/sharepoint2010customization/thread/32d32e47-3256-4806-8775-c250b6243038) . Or, you can place a script on the page that loop through the HTML nodes and replace the unfriendly html tags with a hyperlink as described here http://practicalsharepoint.blogspot.com/2011/10/dynamic-hyperlinks-in-calculated.html.

But today, I’m going to show you how to trick SharePoint into displaying the hyperlink, and it is going to be very easy.

1. Modify the calculated column and change the returned data type from Single line of text to Number/Currency/Date and Times. Click OK.

2. Go back to the list and be amazed :)

This will also work when you’d like to achieve the same thing with the image tag <img>.

About these ads

38 comments on “SharePoint 2010 calculated column and hyperlink (no workflows or scripts needed)

  1. Hi, works well to display in list view, does not display in form view for some reason.

    I am pulling a list of like-records into a repeat table in new form to allow the user to see other, possibly related records. The ‘URL’ column that shows a good hyperlink in the list using your formula approach, bit it does only show a blank space in the form. Any idea why, or a solution perhaps?

    Thank you.
    Stefan

  2. Pingback: How to set up Red-Yellow-Green status indicators in a task list without any customizations « sharepointyoda

  3. While it is great that there is some way to make this work, And it does for me in 2010. it drives me crazy that you have to do stupid things like this in sharepoint (A LOT). I have been a software dev nearly 30 yrs now and I would be embarrased to put out a product that required such lameness to accomplish a fairly common thing. Why on earth does it even allow you to put a concatenated string into a numeric datatype?

  4. This worked wonderfully for me (SharePoint 2010).

    I discovered a partial workaround for the column alignment issue caused by applying the Number data type (which makes the calculated column right-aligned, leaving a huge white space appear to the immediate left – as shown in your screenshot). If you modify the View Style to “Newsletter, no lines”, the column data will be left aligned (only the column title will be right aligned). That also places the calculated column closer to any preceding columns. This view style is available in both lists and libraries. Another useful View Style option if you are on a list is “Boxed, no labels”.

    Another note: One needs to be very careful with calculated column syntax – that might be responsible for the problems some have noted in previous comments. There’s a useful reference page about using Calculated Field Formulas on the MSDN site at http://msdn.microsoft.com/en-us/library/bb862071(office.14).aspx

  5. Handy tip. Agree with Mike J. I also like the alignment tip from Tod Beane. The header alighment can be fixed up in SHarePoint Designer.

  6. you can wrap your formula with a css-styled DIV to overcome the alignment: =””&Your formula starts and ends between the ampersands&””

  7. Hi, I’m using this solution, but I have a problem to calculate the hyperlink by using a https Link & the column ID. It happens that the ID disapear some times. does anybody have a solution for this? Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s