------ Register to get rid of these "In Post" ads! ------


Good Day,

hope that you possibly can help me.

I tried below function in an Excel sheet. It works well until I change the
text to display for the hyperlink, for e.g. to cnn instead of
http://www.cnn.com.

Then the link doesn’t work anymore. For short links this is no issue, but
'Original Source: The Code Cage Forums http://www.thecodecage.com/forumz/worksheet-functions/110357-excel-hyperlink-text-display-vlookup.html#post395073
unfortunately I have major long links to follow
and would love to make them more user friendly by shortening the displayed
text. Is this possible at all?

I seriously appreciate your help.
Thank you and best regards, Kerry




> Start with a table in M1 thru N5:
>
> 1 http://www.cnn.com
> 2 http://www.abc.com
> 3 http://www.nbc.com
> 4 http://www.cbs.com
> 5 http://www.fox.com
>
> In A1 put your data validation dropdown covering the list in column M
> In B1 put:
>
> =HYPERLINK(VLOOKUP(A1,M1:N5,2,FALSE))
>
> The VLOOKUP picks the right link and the HYPERLINK function makes the result
> "click-able". Enjoy.