I have a number of URLs that I first convert from text to the =HYPERLINK-function and then want to edit the title-section of this function, so that only the domain is visible in the cells. I attached the spreadsheet with my scripts. [Spreadsheet][1]

First I run "Convert Hyperlink", which produces the =HYPERLINK-function but when I run "Convert Title" then I'm back at my old text-urls. I want the result to look like =HYPERLINK("url";"www.domain.com") Also the script doesn't differentiate between different top-level-domains, which I wanted to by-pass by editing the script after a successful run, but is there another way?

I will include examples incase the Spreadsheet is not available in the future:
The sheet include various urls from different websites in column A

function converttolink() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var row = 1;
var val = sheet.getRange(row, 1).getValue();
while (val != "!")
if (val == "")
{
row++;
val = sheet.getRange(row, 1).getValue();
}
else
{
var val = sheet.getRange(row, 1).getValue();
var h_string = '=HYPERLINK("'+val+'";"'+val+'")';
cell = sheet.getRange(row, 1).setValue(h_string);
row++
}
}

With the second script I try to edit the part after the ; in the =HYPERLINK-function with this script:

function converttitle() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var row = 1;
var url = sheet.getRange(row, 1).getValue();
while (url != "!")
if (url == "")
{
row++;
url = sheet.getRange(row, 1).getValue();
}
else
{
var url = sheet.getRange(row, 1).getValue();
myurl= url.replace("'.de'\?.*?html", ".de");
cell = sheet.getRange(row, 1).setValue(myurl);
row++
}
}

I realized while writing, that the getValue function will only get the actual url-text and not the =Hyperlink-function and therefore it only writes url-text back into the cell, just like it was before the conversion into the =HYPERLINK-function.
I guess it would be easier to truncate the urls first and write it in a second column and then change the first script to use the full and the truncated url to form the =HYPERLINK-function. [1]: https://docs.google.com/spreadsheets/d/1Q2SOXstc8s7f3jKRKoW2jIh1ULoyzieBvwQBKJcdSZI/edit?usp=sharing

I have a number of URLs that I first convert from text to the HYPERLINK function and then want to edit the title-section of this function, so that only the domain is visible in the cells. I attached the spreadsheet with my scripts. Spreadsheet

First I run "Convert Hyperlink", which produces the HYPERLINK function but when I run "Convert Title" then I'm back at my old text URLs. I want the result to look like =HYPERLINK("url";"www.example.com"). Also the script doesn't differentiate between different top-level-domains, which I wanted to bypass by editing the script after a successful run, but is there another way?

I will include examples in case the Spreadsheet is not available in the future:
The sheet include various URLs from different websites in column A

function converttolink() {  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");  
  var row   = 1;  
  var val   = sheet.getRange(row, 1).getValue();  
  while (val != "!")  
    if (val == "")  
    {  
      row++;  
      val = sheet.getRange(row, 1).getValue();  
    }  
    else  
    {  
      var val   = sheet.getRange(row, 1).getValue();  
      var h_string = '=HYPERLINK("'+val+'";"'+val+'")';  
      cell = sheet.getRange(row, 1).setValue(h_string);  
      row++  
    }  
}

With the second script I try to edit the part after the ; in the HYPERLINK function with this script:

function converttitle() {  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");  
  var row   = 1;  
  var url   = sheet.getRange(row, 1).getValue();  
  while (url != "!")  
    if (url == "")  
    {  
      row++;  
      url = sheet.getRange(row, 1).getValue();  
    }  
    else  
    {  
      var url = sheet.getRange(row, 1).getValue();  
      myurl= url.replace("'.de'\\?.*?html", ".de");  
      cell = sheet.getRange(row, 1).setValue(myurl);  
      row++  
    }  
}

I realized while writing, that the getValue function will only get the actual URL text and not the Hyperlink function and therefore it only writes url-text back into the cell, just like it was before the conversion into the HYPERLINK function.

I guess it would be easier to truncate the URLs first and write it in a second column and then change the first script to use the full and the truncated URL to form the HYPERLINK function.

I have a number of URLs that I first convert from text to the =HYPERLINK-function and then want to edit the title-section of this function, so that only the domain is visible in the cells. I attached the spreadsheet with my scripts. Spreadsheet

First I run "Convert Hyperlink", which produces the =HYPERLINK-function but when I run "Convert Title" then I'm back at my old text-urls. I want the result to look like =HYPERLINK("url";"www.domain.com") Also the script doesn't differentiate between different top-level-domains, which I wanted to by-pass by editing the script after a successful run, but is there another way?

I have a number of URLs that I first convert from text to the =HYPERLINK-function and then want to edit the title-section of this function, so that only the domain is visible in the cells. I attached the spreadsheet with my scripts. [Spreadsheet][1]

First I run "Convert Hyperlink", which produces the =HYPERLINK-function but when I run "Convert Title" then I'm back at my old text-urls. I want the result to look like =HYPERLINK("url";"www.domain.com") Also the script doesn't differentiate between different top-level-domains, which I wanted to by-pass by editing the script after a successful run, but is there another way?

I will include examples incase the Spreadsheet is not available in the future:
The sheet include various urls from different websites in column A

http://100-jahre-musikverein.de/
http://145789.guestbook.onetwomax.de/?sn=12
http://165870.forumromanum.com/member/forum/entry_ubb.user_165870.2.1108698443.1108698443.1.viele_fotos_aus_indien-indienforum.html
http://2015.mercedescup.de/ENG/Media/Videos/Day6MercedesCup2015/
etc

The result should look like this:

=HYPERLINK("http://165870.forumromanum.com/member/forum/entry_ubb.user_165870.2.1108698443.1108698443.1.viele_fotos_aus_indien-indienforum.html";"http://165870.forumromanum.com")
=HYPERLINK("http://145789.guestbook.onetwomax.de/?sn=12";"http://145789.guestbook.onetwomax.de")

So that it looks in Spreadsheet just like this:

http://165870.forumromanum.com
http://145789.guestbook.onetwomax.de

But still leads to the original link when clicked on.

The script I used to convert the unclickable text-links to the =HYPERLINK-function is:

function converttolink() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var row = 1;
var val = sheet.getRange(row, 1).getValue();
while (val != "!")
if (val == "")
{
row++;
val = sheet.getRange(row, 1).getValue();
}
else
{
var val = sheet.getRange(row, 1).getValue();
var h_string = '=HYPERLINK("'+val+'";"'+val+'")';
cell = sheet.getRange(row, 1).setValue(h_string);
row++
}
}

With the second script I try to edit the part after the ; in the =HYPERLINK-function with this script:

function converttitle() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var row = 1;
var url = sheet.getRange(row, 1).getValue();
while (url != "!")
if (url == "")
{
row++;
url = sheet.getRange(row, 1).getValue();
}
else
{
var url = sheet.getRange(row, 1).getValue();
myurl= url.replace("'.de'\?.*?html", ".de");
cell = sheet.getRange(row, 1).setValue(myurl);
row++
}
}

I realized while writing, that the getValue function will only get the actual url-text and not the =Hyperlink-function and therefore it only writes url-text back into the cell, just like it was before the conversion into the =HYPERLINK-function.
I guess it would be easier to truncate the urls first and write it in a second column and then change the first script to use the full and the truncated url to form the =HYPERLINK-function. [1]: https://docs.google.com/spreadsheets/d/1Q2SOXstc8s7f3jKRKoW2jIh1ULoyzieBvwQBKJcdSZI/edit?usp=sharing

I have a number of URLs that I first convert from text to the =HYPERLINK-function and then want to edit the title-section of this function, so that only the domain is visible in the cells. I attached the spreadsheet with my scripts. Spreadsheet

First I run "Convert Hyperlink", which produces the =HYPERLINK-function but when I run "Convert Title" then I'm back at my old text-urls. I want the result to look like =HYPERLINK("url";"www.domain.com") Also the script doesn't differentiate between different top-level-domains, which I wanted to by-pass by editing the script after a successful run, but is there another way?

Thanks and best regards

I have a number of URLs that I first convert from text to the =HYPERLINK-function and then want to edit the title-section of this function, so that only the domain is visible in the cells. I attached the spreadsheet with my scripts. Spreadsheet

First I run "Convert Hyperlink", which produces the =HYPERLINK-function but when I run "Convert Title" then I'm back at my old text-urls. I want the result to look like =HYPERLINK("url";"www.domain.com") Also the script doesn't differentiate between different top-level-domains, which I wanted to by-pass by editing the script after a successful run, but is there another way?