Append the cell content as a URL parameter and create a link

Question

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

Answer 1

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