Google spreadsheet formula for url with a variable

Question

What's the formula to display an url in google sheet when part of said url is a variable from another cell?

I'll explain: in one cell I have this formula (used to scrape text with class 'description' in an html webpage, doesn't matter for the purpose of the question though) =importXML("http://www.xxx.it/code/9788823506183/doc/book.html","//span[@class='description']")

The numerical part changes everytime based on the value in another cell, say B3. Tried =importXML("http://www.xxx.it/code/(B3)/doc/book.html","//span[@class='description']") but of course it won't work.

Then I thought I could dinamically create the url in B2 and do like this: =importXML("B2","//span[@class='description']")

One step closer but I'm stuck anyway, how can I generate such url in B2? I can't find a suitable function in the docs. I could only come up with:

in A1 = 9788823506183

in B2 = http://www.xxx.it/code/("A1")/doc/book.html but it's not the correct syntax. :(

Answer 1

In Excel, you'd do something like the following. Give it a try in a google spreadsheet.

=importXML("http://www.xxx.it/code/" & B3 & "/doc/book.html","//span[@class='description']")

Answer 2

Use CONCAT in Google Sheets.

=CONCAT("http://www.xxx.it/code/(", $B2,")/doc/book.html")

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

Answer 3

The following example starts a timer based on a cell value but could be adapted to build a URL out of any cell values. Assume that there is a number of minutes that you want to make a timer for is in $C20

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

=CONCAT("https://www.google.com/search?q=set+timer+for+", $C20)

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

=CONCAT($E27,"+minutes")

3) Make a cell that contains a hyperlink that will call up the timer:

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

Then when you click on the cell you made in #3 a hyperlink will hover over and you can click on it.