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:

13 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.

    • Alex January 10, 2018 / 11:16

      The problem with this is if your SharePoint list already exists with a specific set of fields already defined in the list. Import Spreadsheet as mentioned in your comment is much like how Access will imports a table, and the problem with it is that it will create the SharePoint list and guess at the data type of the fields to create in the list based upon the data in the Excel file (or Access table). So, Daniel’s blog information is critical to know if you want to import Excel file with hyperlinks to an existing list by appending the data to the list.

      Here it is in 2017 with me using SharePoint in O365 to move 1,000’s of items from one list to another (i.e. migrate the list content) between site collections when I am not a site collection admin and cannot save or add the list as a template with data, and this blog post is a critical to understand how to make that process work.

      • Alex January 10, 2018 / 12:32

        There is one piece of this puzzle that is missing. What if your Excel file has a column of hyperlinks far too large to manually edit all of them to get them into the needed format that Daniel explains? What we need is an automated way to convert them to the necessary format he describes. This seems to only be able to be done with a custom VBA macro. See this blog post:

        The module code near the bottom of that post is close to what we need, but not quite. It is outputting a concatenation not useful to our purpose. What we need is this:

        Function GetURL(cell As Range, _
        Optional default_value As Variant)
        ‘Lists the Hyperlink Address for a Given Cell
        ‘If cell does not contain a hyperlink, return default_value
        If (cell.Range(“A1”).Hyperlinks.Count 1) Then
        GetURL = default_value
        GetURL = cell.Range(“A1”).Hyperlinks(1).TextToDisplay & “#” & cell.Range(“A1”).Hyperlinks(1).Address
        End If
        End Function

  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: Logo

You are commenting using your 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