Paste a url together with title (Display Text) in Quick Edit mode

Question

I have several hundred urls I need to paste in to a Sharepoint list. I can paste the urls with no problem, but I need to paste in a Title too (or 'Display Text' to give the exact system name for the field). I like to be able to paste in the 'Display Text' at the same time as pasting the url. I'm working in Edit / Quick Edit mode, rather than the standard mode.

When in Edit mode, I can click in the url field and it will allow me to manually type in the Display Text, but that'll take ages and cause me to lose the will to live.

Research

I've found a couple of posts with proposed solutions, neither of which work for me.

Link 1 and Link 2. This third link also looked useful, it involves using a workflow. *New addition: Link 4 and Link 6. (No, there's no Link 5).

Link 2 Results

The second of the links above looked especially promising. In Excel I pasted in the formula they've used:

=CONCATENATE(“”””,B2,””””,”, “,””””,,A2,””””)

Yet the result I get is a #NAME? error.

Here's a screenshot of what I see in Excel. The formula looks ok to, Excel appears happy to accept it, yet gives a bad result.

Link 3 Results

The workflow failed to paste the values correctly; the display text contains both the url and the 'Display Text'.

Link 4 Results

THis post has two answers, Sergei's accepted answer has since been deprecated by Microsoft (html no longer permitted in calculated columns). I tried out the second answer, by using a calculated column, but this only returned the value of the text field I'm using as a title for the url.

5. Ganesh's suggestion

Thanks for posting Ganesh. I tried out this method, I'm using Excel. Please note the formula that created the url. As you see in the 2nd and 3rd pictures below, the value 'I am the Title' is posted to both fields, the url has not been pasted in.

Link 6 Results

(I don't have the correct version of Access to be able to attempt this method by Daniel Šmon, but am posting for reference for others facing the same issue as me. Yes, I'm selfless I know I know...)

Answer 1

For the Link2, you could try this formula, this works for me.

=CONCATENATE("""",B3,"""",", ","""",A3,"""")

Answer 2

This will let you paste ...code! As you are on SP2013 this should work as you still have access to sp.js

The following script will retrieve and loop through all items in the list, combining two columns: URL and URLText into the third column Link for each item. Finally an update is made to the batch.

If you update the list and column names you should be able to paste this into the console of dev tools. See screenshot below code.

function updateListItems() {
    var clientContext = SP.ClientContext.get_current();
    var oList = clientContext.get_web().get_lists().getByTitle('URL');
    var camlQuery = new SP.CamlQuery();
    this.collListItem = oList.getItems(camlQuery);
    clientContext.load(collListItem);
    clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
}

function onQuerySucceeded(sender, args) {
    var listItemEnumerator = collListItem.getEnumerator();
    var clientContext = SP.ClientContext.get_current();
    var oList = clientContext.get_web().get_lists().getByTitle('URL');        
    while (listItemEnumerator.moveNext()) {
        var oListItem = listItemEnumerator.get_current();
        var urlValue = new  SP.FieldUrlValue();
        urlValue.set_url(oListItem.get_item("URL"));
        urlValue.set_description(oListItem.get_item("URLText"));
        updateitem = oList.getItemById(oListItem.get_id());
        updateitem.set_item("Link", urlValue);     
        updateitem.update();  
    }
    clientContext.executeQueryAsync(Function.createDelegate(this, ()=>alert("OK!")), Function.createDelegate(this, ()=>alert("FAILED :(")));
}

function onQueryFailed(sender, args) {
    alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
}

updateListItems();

Answer 3

"yoururl", "your display"

can be pasted from Excel. The double quotes must exist in pasted text and there must be a space after the comma. You can accomplish this with an Excel formula, just typing in the literal string, or if the display text is part of the URL, by custom cell format.

I used the latter, as the URLs for me are all the same, except for an identifier, so to make it easy for data entry, a user would just type in the cell the identifier, and Excel would display it as the "URL", "display" format, which gets picked up with copy/paste.

e.g. in number format section of cell format dialog

    \""https://somedomain.com/someReallyLongString/"@\", \"@\'

where \ is an escape character so formatting prints the double quotes. @ is the value entered in the cell, used both in the URL and the display text.

So if a user enters 123 into a cell, it would both display and copy/paste as "https://somedomain.com/someReallyLongString/123", "123"

obviously, you could get more complex results if using a formula to get string values from other places than the cell where user enters data (for example, if your display text is not embedded in the URL string).