Use array as function parameters

Question

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.)

Answer 1

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))

Answer 2

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))) 

Answer 3

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