(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?
Best Answer
This can be done with
arrayformula
: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 ofif(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 ofisblank
andquery
commands. (When a string is required, i.e., for string comparison, blank cells are coerced to empty strings; this is whyB1: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; otherwiseregexreplace
chokes up on numeric input such as 1234.567In almost all cases, concatenation is easier expressed with & operator than with
concatenate
command. Less typing, fewer parentheses to mess up.