Google-sheets – Append the cell content as a URL parameter and create a link

google sheetsgoogle-sheets-arrayformulalinks

Here's a sample form:
https://docs.google.com/forms/d/e/1FAIpQLSfiPSHacKKNEfe-Jq82DBScbCBRkwtm8rq2Jq7y-ceLgGIC3Q/viewform

And here's the linked spreadsheet:
https://docs.google.com/spreadsheets/d/16mNc5EBNRBZ_E3jWa848NhFUNoTu496_lBSjb4Rd7x0/edit

I've created an extra sheet named Comments to draw data from the Source sheet. The data on the second sheet should be exactly like the first sheet, except column B of the Comments sheet: I'd like the usernames to be hyperlinks using the following URL:
https://t.me/username
As a result, for the username @johnDoe on sheet 1, I should have the following on sheet 2:
@johnDoe

Best Answer

You can do this very easily with a literal array and array formula: 

={Source!A1:C1;Source!A2:A,arrayformula(if(istext(Source!B2:B),Hyperlink("https://t.me/"&substitute(Source!B2:B,"@",""),Source!B2:B),)),Source!C2:C}

The first part creates the header, the below it it pulls in the first column, then uses and if check to only hyperlink fields in column B that have text in it, then pulls column C.

This way you only enter the formula once at the top and never have to edit it again

enter image description here

Related Topic