4

(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?

4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy