How do I concat column and then rows in same formula

Occasional Contributor


My table looks like this.


I want to parse this table into a value like this.


=HYPERLINK('https://example/?data= A1:B1,A2:B2,A3:B3,A4:B4`')


Note: All these cell values need to be encoded through ENCODEURL first before becoming part of the URL.

3 Replies



You've had nearly 50 views and no response. I suspect the issue is that  your description needs more clarity, so people are just passing on it.


I don't think, for starters, your table really (literally) looks like your example. There must be values in A1 and B1 (etc) that, when concatenated and appended to the " ('https://example/?data= " will end up in a valid URL of some kind. Right?


For a next part, do you really expect the colon between whatever A1 and B1 are?

And then do you expect A2 and B2 and so forth all to be concatenated into a llloooonnnnngggg URL?


Now, if all you're asking is how to concatenate those illustrated values--which I doubt is the case, since you know about ENCODEURL--it would be A1&":"&B1&","&A2&":"&B2&",".....


It would be easier to give solid advice if you gave us the real thing, or if, for some reason, the real thing is proprietary, then a realistic alternative. In fact, if you could post the actual file (or a mockup) on OneDrive or GoogleDrive, pasting a link here that grants edit access, that would help us help you.

best response confirmed by VarunAgw (Occasional Contributor)

@VarunAgw  attached are 2 options that might work for you. the 'key' part is:


in that it will give you the A1:B1,A2:B2 ...

wasn't sure if the encodeurl was going around the whole thing (I doubt) or applied to each individual cell (more likely):




Thank you for the comment. The other solution worked for me so I am not adding any more details now.