evaluate HYPERLINK()

[Guest]

I use Microsoft Query to query a database and return data back to excel. One
column returned contains data for hyperlinks. In SQL, the column returns a
text string '=HYPERLINK("http://www.microsoft.com", "MSFT")'. Instead of
displaying "MSFT" in the excel cell, the whole string (i.e.
"=HYPERLINK(....)") is displayed.

Is there a way to force evaluation of the string contained in a cell?

 

[Guest]

To be honest, I'm not sure how to evaluate a string that represents a formula
(although if there is nothing to force the cell to be text (eg a leading '),
calculate now?).

However, since nobody has replied yet, I thought I'd give a possible work
around.

If you are prepared to use another column, you could derive what you are
after from that - this is a bit crude and is dependant on your web address
having no "," in it.

Say the text is in cell A1. The following formula should return the text you
desire:

=MID(LEFT(A1,LEN(A1)-2),SEARCH(",",A1)+3,258)

I have assumed that there will always be a space after the comma in the
formula (coming from a single source where the formula is actually used, I
would say this would be consistent). The 258 allows for the 255 character
limit plus the leading , "

This would only give you the text - if you actually wanted to keep the
hyperlink, you can use:

=HYPERLINK(RIGHT(LEFT(A1,A4-2),A4-A3-3),MID(LEFT(A1,LEN(A1)-2),SEARCH(",",A1)+3,258))

Oh, and if there is actually no space after the comma, replace (",",A1)+3 in
the code with (",",A1)+2

I know it is crude, but until someone comes up with a solution, maybe this
will help

Baz