Hyperlinks

excel_training_wheels

Board Regular
Joined
Aug 25, 2005
Messages
193
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!
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
excel_training_wheels said:
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.:

Code:
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

Board Regular
Joined
Aug 25, 2005
Messages
193
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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

Board Regular
Joined
Aug 25, 2005
Messages
193

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Nothing too major:

Where this statement references A65536
Code:
LastRow = Range("A65536").End(xlUp).Row
just change the A to whatever column you want/need.

Then where you see
Code:
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,089
Members
412,310
Latest member
mark884
Top