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:
The trouble with pasting links with text is that only the display text is pasted, and not the underlying hyperlink:
I tried a few obvious things, like using delimiters such as the following, without any luck.
- Microsoft#;http://www.microsoft.com
- Microsoft, http://www.microsoft.com
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:
The Link column has the formula:
=CONCATENATE([@Title], "# ", [@URL])
Once the spreadsheet is set up, you can open the list in Access:
And paste:
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:
Šmonland 
Reblogged this on SutoCom Solutions.
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.
Thanks for the comment JC, that certainly sounds quicker and simpler than going through Access like I did.
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.
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: http://howtouseexcel.net/how-to-extract-a-url-from-a-hyperlink-on-excel.
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
Else
GetURL = cell.Range(“A1”).Hyperlinks(1).TextToDisplay & “#” & cell.Range(“A1”).Hyperlinks(1).Address
End If
End Function
Hi and another way using excel to create the link to paste… https://toepoint.wordpress.com/2016/01/07/pasting-links-into-a-sharepoint-2013-list-using-quick-edit/
Thank you. This is a time saver
Thank you. Saved the day!
Saved the day. The other excel ways didn’t work for me. Thank you!
Awesome. You save the day for me. I appreciate bro.