Hyperlinks in Excel

[hoyaguru]

This one is kind of hard to explain. I am getting huge lists, tens of thousands, of hyperlinks from web pages. Each link is a part number that corresponds to another number. For example, a link might look like:

123-456-007

and when you click on that link, it directs you to a page where it explains that the part number you clicked on is actually 7777-888. Now, the hyperlink itself will be something like www.site.com/7777.888.htm. I can copy page after page of these links, and I can put my curser on each one and manually type each real number next to the original hyperlink. What would be much easier would be to copy the hyperlinks into Excel, get a big long column of them, copy the column right next to itself, then change the second column somehow so it spells out each hyperlink. Then I could just replace the www.site.com/ and the .htm with nothing, and voila, I have both part numbers side by side and I've saved a month of typing numbers. The problem is, I can't figure out how to change a hyperlink in Excel to show the actual hyperlink. I'm guessing it either can't be done, or it's monumentally simple and I just can't figure it out. Any ideas? Would it be easier if I were copying these hyperlinks into a different program?

 

[Ioo]

is your original in HTML or text file of some sort?

if so you can just do a replace with notepad and clear out all the formatting stuff and get the same result

 

[hoyaguru]

I figured it out, wrote a little macro (with some help I found online):

Range("B1").Select
Dim Text As String
Text = ActiveCell.Hyperlinks.Item(1).Address
Range("B1") = Text

then it finishes by copying the hyperlink to a new page along with the original hyperlink. Then I just repeat the macro however many times I need to, then copy the new page as a text file, bring it back into Excel, and go from there.