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