Automatically create hyperlinks with parameters, based on data in another column

Question

(This question was posted here by Tasos, but then deleted). I quote a part of it:

---

At the moment, I have a cell which used to create a hyperlink on the next column. This is what I have done at the moment.

The first cell (in B4 for example) has this format 123456.789 and the hyperlink:

=IF(B4="","",HYPERLINK(CONCATENATE("http://www.example.com/?tag1=",LEFT(B4, FIND(".", B4)-1) ,"&tag2=",RIGHT(B4, Len(B4)-FIND(".", B4)) ), B4)) 

The result of the link will be: http://www.example.com/?tag1=123456&tag2=789

However, I have to update the column with the hyperlink manually, every time. How can I make it so that every time an entry is added to column B, a hyperlink is created?

Answer 1

This can be done with arrayformula:

=arrayformula(if(B1:B="",,hyperlink("http://www.example.com/?tag1=" & regexreplace(""&B1:B, "\.", "&tag2="), B1:B)))

The main changes are: passing in the entire column B1:B and wrapping the formula in arrayformula. This will result in it being automatically applied to every cell in B. (If you have a header row, replace B1:B by B2:B everywhere.)

But I also made a few improvements to the formula.

• I use if(B1:B="",,hyperlink(...)) instead of if(B1:B="","",hyperlink(...)) The first version leaves the cell blank, the second puts empty string in it. These are subtly different states of a cell: the difference is invisible but affects the outcome of isblank and query commands. (When a string is required, i.e., for string comparison, blank cells are coerced to empty strings; this is why B1:B="" matches both blank cells and those with empty string.)

• Instead of a bunch of find, left, right, len, I used substitution: regexreplace(""&B1:B, "\.", "&tag2=") replaces dot with "&tag2=". (This assumes that you have only one dot in the input, otherwise the input is ambiguous.) An interesting detail: the concatenation ""&B1:B is needed to coerce the input to string form; otherwise regexreplace chokes up on numeric input such as 1234.567

• In almost all cases, concatenation is easier expressed with & operator than with concatenate command. Less typing, fewer parentheses to mess up.