0

Given a function that returns an array, is it possible to "spread" the return values as the arguments to another function?

For example, I'd like to pass the result of SPLIT("example.com,My Site", ",") to the HYPERLINK(url, [link_label]) function and have it treat "My Site" as the link_label parameter.

I've tried

=HYPERLINK(SPLIT("example.com,My Site", ","))

but it ignores the second argument.

(This is a simplified example. In reality, the function that's returning the URL and label is a QUERY which I'd like to avoid repeating in the second argument.)

5
  • Wrap your formula in ARRAYFORMULA() like =ARRAYFORMULA(HYPERLINK(SPLIT("example.com,My Site", ","))) – Oleg_S Oct 1 '20 at 13:32
  • @OlegS that doesn't work I'm afraid. It returns two cells – one with the link and no label, and one with the label and no link. – Joe Taylor Oct 1 '20 at 13:40
  • =ARRAYFORMULA(HYPERLINK( QUERY(SPLIT("example.com,My Site", ","),"Select Col1"), QUERY(SPLIT("example.com,My Site", ","),"Select Col2"))) – Oleg_S Oct 1 '20 at 13:44
  • Now the function call to get the arguments is repeated, which is what I'm trying to avoid. – Joe Taylor Oct 1 '20 at 14:03
  • 1
    HYPERLINK() requires 2 arguments, you can't avoid that. – Oleg_S Oct 1 '20 at 14:27
0

Your split string is SPLIT("example.com,My Site", ","). The url is the first element, and the label is the second element.

Use Index to retrieve the separate elements of the split value. The paramaters returns the content of a cell, specified by row and column offset.

  • First element: =index(SPLIT("example.com,My Site", ","), 0, 1) (row zero, column 1)
  • Second element: =index(SPLIT("example.com,My Site", ","), 0, 2) (row zero, column 2)

Combine them in the hyperlink:

=HYPERLINK(INDEX(SPLIT("example.com,My Site", ","), 0, 1), INDEX(SPLIT("example.com,My Site", ","), 0, 2))

0

Google Sheets hasn't a "spread" operator. AFAIK there isn't a workaround for that when using built-in functions.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.