How to paste hyperlinks with descriptions in SharePoint 2013 and SharePoint Online

A client recently wanted to copy and paste hyperlinks with friendly descriptions from Excel in to the Hyperlink column of a list in SharePoint online.

This used to work back in SharePoint 2010 with Datasheet view because it used an ActiveX control that provided rich functionality. Since SharePoint 2013, the Datasheet view has been replaced with the Quick Edit view, which is HTML and JavaScript only. While this is great for browser compatibility, it does mean that the Quick Edit view has less functionality than the ActiveX control used to have.

Another piece of functionality that is missing, is the ability to update list data from Excel. You can create a new list from an existing spreadsheet, you just can’t update an existing one.

I should also point out that it’s entirely possible to copy and paste Excel cells that contain hyperlinks, as long as it’s just the links themselves. To be clear:

Links that can be copied in to SharePoint Quick Edit view
These links can be copied in to Quick Edit view in SharePoint
Links that can't be copied in to SharePoint Quick Edit view
These links can NOT be copied in to Quick Edit view in SharePoint

The trouble with pasting links with text is that only the display text is pasted, and not the underlying hyperlink:

Error when pasting in Quick Edit view
Error when pasting in Quick Edit view

I tried a few obvious things, like using delimiters such as the following, without any luck.

After looking at various JavaScript based paste options, something made me look at the Access integration with SharePoint lists. At first, this didn’t seem to work either, but after a little digging, and finding an old article on pasting hyperlinks in Access 2007 by John Garger, I found the solution.

First, you need to set up your spreadsheet to have the description and links delimited with a #. I did this with a simple formula in Excel:

Links with # delimiter for pasting in to Access
Links with # delimiter for pasting in to Access

The Link column has the formula:

=CONCATENATE([@Title], "# ", [@URL])

Once the spreadsheet is set up, you can open the list in Access:

Open with Access on the Ribbon
Open with Access on the Ribbon

 

And paste:

Pasting in Microsoft Access linked to SharePoint Online
Pasting in Microsoft Access linked to SharePoint Online

Some comments on pasting. Firstly, you must make sure that the columns match up of course. In addition to this, you’ll need to select the cells in the columns that you want to paste data in to. If you don’t select all of the cells, the text will be pasted in the individual cell where your cursor is located. In the above example, you would click in the cell of the new row in the Title column, then hold Shift and click on the cell in the same row under the Link column. I recorded a short video that explains this:

11 thoughts on “How to paste hyperlinks with descriptions in SharePoint 2013 and SharePoint Online

  1. JC Horton October 26, 2015 / 23:39

    Thanks so much! Great post! I discovered an easier way to do this in SP 2013. Export your list to an excel spreadsheet. Put it on your desktop. Then go to site content (the place you want to import the list) and find the app “import spreadsheet”. Simply find your spreadsheet, and import it. Then modify the list view to include “Link Display Name” only, and you’re done. Took me seconds to migrate an extensive links list to a new site.

    • Daniel Šmon October 27, 2015 / 08:23

      Thanks for the comment JC, that certainly sounds quicker and simpler than going through Access like I did.

  2. Editha Tiamzon May 11, 2016 / 23:40

    Thank you. This is a time saver

  3. Rick Virk June 21, 2016 / 07:52

    Thank you. Saved the day!

  4. Cody December 10, 2016 / 13:50

    Saved the day. The other excel ways didn’t work for me. Thank you!

  5. Olu August 17, 2017 / 06:09

    Awesome. You save the day for me. I appreciate bro.

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