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, 2020 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, 2020 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, 2020 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, 2020 at 14:03
  • 1
    HYPERLINK() requires 2 arguments, you can't avoid that.
    – Oleg_S
    Oct 1, 2020 at 14:27

3 Answers 3

1

Sheets' addition of the LET function enables storing intermediary calculation and addresses your need to limit repetition.

LET

QUERY(...) is a placeholder for your other formula.

=LET(q,QUERY(...), ln,FIND(",",q), 
   HYPERLINK(LEFT(q, ln-1), MID(q, ln+1, LEN(q)))) 

Alternative using INDEX and SPLIT as in Tedinoz's answer

=LET(q, SPLIT(QUERY(...), ","),
   HYPERLINK(INDEX(q,,1), INDEX(q,,2))) 
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 and acknowledge you have read our privacy policy.

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