Is there a way to reference the URL stored in another cell (as a hyperlink) without extracting via VBA or similar?

[IneptPig]

Hi all,

I have an Excel sheet with a selection of hyperlinks included; what I'd like to do is concatenate the various parts of information, but then retain the hyperlink from a specific column.

Is there a way to do this? As the links are not stored on the excel sheet as their actual location (but things like 'home', 'search' but with a clickable URL) then the hyperlink function doesn't seem to work as it just returns the text and not the link.

Am I missing something obvious?

Thanks!

 

[Legacy 143009]

Yes, hyperlink function just does this.

=HYPERLINK(your_concatenated_text, your_hyperlink)

 

[IneptPig]

Sorry, to expand and explain as perhaps I didn't do the job properly, if I have this:

Location	Region	Active?
Home	Asia	No

and then want to the to be merged into a single version - Concat(a1, " / ", b1, "/ Active: ", c1), so the text would be: Home / Asia / Active: No

but if I want to use the hyperlink from that first column, it doesn't work, as it's not the actual web-address that it would take - instead it would just try to go to 'Home' (which doesn't exist).

 

[Legacy 143009]

It is quite şmpossible without VBA.

Create a standard module in vba window. In standart module inset the following function:

Function GETURL(Hyperlink As Range)
  GETURL= Hyperlink.Hyperlinks(1).Address
End Function

Close VBA window.

In formula bar you can use like:

=HYPERLINK(Concat(a1, " / ", b1, "/ Active: ", c1), GETURL(a1))

 

[IneptPig]

Thanks - I was aware of GETURL but just wondered if there was a way around it without VBA (as it's a spreadsheet I don't have full control over, just end data tinkering).

Will use VBA!