Hyperlinks

[excel_training_wheels]

Hi--

I have a workbook with hyperlinks included. I would like to VLOOKUP and have the underlying URL returned, rather than the hyperlink. Is there a way to do that?

--OR--

Is there a way to convert the entire column of hyperlinks to just have the URL, and then my VLOOKUP will be fine.

In otherwords, I want to see http://...... instead of E09887.htm when the lookup is complete.

Please help!

 

[Von Pookie]

Is there a way to convert the entire column of hyperlinks to just have the URL, and then my VLOOKUP will be fine.

Assuming the links are in column A, this should work. This will change the text in the cell to display the address of the link (if there is one). This will leave the cell linked, as well.:

Sub test()
Dim LastRow As Long, i As Long

LastRow = Range("A65536").End(xlUp).Row

For i = 1 To LastRow
    On Error Resume Next
    Cells(i, 1).Value = Cells(i, 1).Hyperlinks(1).Address
Next i

End Sub

 

[excel_training_wheels]

Thanks Kristy.

I am pretty new to Excel, and and not sure what to do with the code you provided. Where do I paste that in excel? In the column adjacent to the links?

 

[Von Pookie]

Ah, sorry. No, it is not a formula, it is a macro. You need to put it in a module.

Go to Tools\Macro\Visual Basic Editor (or press Alt+F11) to open said Visual Basic Editor (VBE).

From there, go to the Insert menu and select "Module." Paste the code into the screen that appears. You can then close the VBE and go back to Excel where you can run the macro from Tools\Macro\Macros. Selecting "test" (the current name of the macro) from the list of macros and click "run" to use it.

You should see the links in column A change while the code is running.

 

[excel_training_wheels]

WOW--- Awesome!

What would code would I change if the links were in column H, or M instead of A for example?

(Last question, promise!)

Thanks sooooooooo much Kristy!

 

[Von Pookie]

Nothing too major:

Where this statement references A65536

LastRow = Range("A65536").End(xlUp).Row

just change the A to whatever column you want/need.

Then where you see

Cells(i, 1)

in the code, change the 1 to the same column letter. So if you used H65536, you would change this to Cells(i,"H")

 

[excel_training_wheels]

Thanks again, happy holidays! You just helped me more than you know!