Extracting embedded URL

[jwreath]

Hello, first time on here, so bear with me. I would like to find a way to extract an embedded URL from a cell (with "friendly text") by means of built in excel functions (no vba). Ultimately, I have a list of links on one sheet (all with "friendly text") and I would like to reference those cells on a different sheet but keep the link functional. Might there be a way to do this? Thanks for any help you all can provide!

 

[Dan_W]

After many years on this forum, I've long since learnt never to say "X is impossible", because invariably someone will come along and make you look stupid by demonstrating that it is in fact, possible.

That said, without VBA or some other programming language, I'm not sure that its possible to do what you're asking. But I'm happy for someone to prove me wrong.

 

[jwreath]

After many years on this forum, I've long since learnt never to say "X is impossible", because invariably someone will come along and make you look stupid by demonstrating that it is in fact, possible.

That said, without VBA or some other programming language, I'm not sure that its possible to do what you're asking. But I'm happy for someone to prove me wrong.

Hi Dan. Yes, I have done some custom function calls, etc to accomplish this in the past. I was just reaching out to see if I could steer clear of the VBA calls and still accomplish the task. Thx for the response! Hopefully somebody can enlighten me!!

 

[RoryA]

Assuming you are talking about a static hyperlink, then the answer is no. (he said, not at all nervously)

 

[jwreath]

Assuming you are talking about a static hyperlink, then the answer is no. (he said, not at all nervously)

Yes, static hyperlink. Thanks, I can stop going crazy now

 

[felixstraube]

Would this work?:

Hyperlink in column A, and this formula in C:

=HYPERLINK(A1, A1)

(edited: i post image and formula because XL2BB messes up the original link in column A)

Or am I getting something wrong? Do you have an example of some of the URLs?

 

[felixstraube]

Oh you mean something like this?:

Sorry I'm a little slow today (sigh).

Only thing I can think of is using a third (helper) sheet with a list of the links and the friendly text and reference those in both of your sheets.

 

[jwreath]

Oh you mean something like this?:

View attachment 116317

Sorry I'm a little slow today (sigh).

Only thing I can think of is using a third (helper) sheet with a list of the links and the friendly text and reference those in both of your sheets.

Hello...yes you are correct with your second take. I've dabbled with the 3rd sheet concept and it works but a bit more clunky than I'd like. I appreciate you taking the time to look at it!!

 

[felixstraube]

Sure thing. Can you share your file? Or post a sample with XL2BB?

 

[felixstraube]

If you would have a Sheet3 with a table like this named "T_Links":

Book2.xlsx
	A	B
1	Link	FriendlyText
2	Google	Google
3	Google Maps	Google Maps
4	Google Earth	Google Earth
Sheet3

In the other sheets you would use:

Book2.xlsx
	A	B
1	Link
2	Google
3	Google Maps
4	Google Earth
5
Sheet1

Cell Formulas
Range	Formula
A2:A4	A2	=HYPERLINK(T_Links[@Link], T_Links[@FriendlyText])

and drag down as needed.

Or if you links and friendly names are not formatted as a table:

Book2.xlsx
	A	B
1	Link
2	Google
3	Google Maps
4	Google Earth
5
Sheet1

Cell Formulas
Range	Formula
A2:A4	A2	=HYPERLINK(Sheet3!$A2, Sheet3!$B2)