Convert pasted URL to standard Hyperlink text automatically

I am looking to understand the following.

A friend of mine (can’t reach him for a while) created a formula that does this:

When you COPY (CTRL+C) a URL from your browser and then go to a cell in an Excel sheet that displays the word LINK (with the formula I copied below in it) and replace the formula (Select all and delete, then CTRL+V to insert the complete URL) it replaces the original LINK word with the same LINK word but as a hyperlink to the actual pasted URL.

=IF($B447>=1;HYPERLINK(VLOOKUP($A447;’DATA’!$A$1:$AA$619;15;FALSE);”LINK”);””)

I know the basics of Excel, IE; the IF is for determining wether a certain column has any text/value in it and although the formula runs through the column it doesn’t display the word LINK on any rows that have no text in Bxxx (which is what I want). I just can’t seem to recreate this function in a new sheet.

I want the cell (in column N) to say SharePoint as soon as text is entered in column A. Then when a user does CTRL+V in that row’s column N with a URL the cell automatically replaces the word SharePoint with a hyperlink to the actual location on SharePoint and automatically changing the link/address to the word SharePoint instead of displaying the full link.

I think I am having a hard time understading how one can remove a formula and then by copying something else (a URL) the actual, removed, formula does its magic.

Any help is appreciated!

By: Rogier van Dam

Leave a Reply

Your email address will not be published. Required fields are marked *