Google spreadsheet formula for URL with variable

What's the formula for displaying a URL in a google sheet when part of the specified URL is a variable from another cell?

I'll explain: in one cell I have this formula (used to clear the class description text "in the html webpage, doesn't matter for the purpose of the question) =importXML("","//span[@class='description']")

The numeric portion changes each time based on the value in a different cell, say B3. Tried =importXML("","//span[@class='description']")

it but of course it won't work.

Then I thought I could dynamically create a URL in B2 and do this: =importXML("B2","//span[@class='description']")

One step closer, but I'm still stuck, how can I create such a url in B2? I cannot find a suitable function in the docs. I could only think of:

in A1 = 9788823506183

in B2 ="A1")/doc/book.html

, but that's not the correct syntax. :(


source to share

3 answers

In Excel, you would do something like the following. Try on google spreadsheet.

=importXML("" & B3 & "/doc/book.html","//span[@class='description']")





in Google Sheets.

=CONCAT("", $B2,")/doc/book.html")


You may need a formula to get the exact position in a string.



The following example starts a timer based on a cell value, but can be adapted to build a URL from any cell values. Suppose there are several minutes that you want to make the timer is at $ C20

1) Create a cell that builds the first part of the url, in my case $ E27:

= CONCAT (" ", $ C20)

2) Make a second cell that completes the URL, in my case $ E28:

= CONCAT ($ E27, "+ minutes")

3) Make a cell containing a hyperlink that will trigger the timer:

= HYPERLINK ($ E28, "Start timer for cell C20 value")

Then when you click on the cell you created in # 3, the hyperlink will hover and you can click on it.



All Articles