Hyperlink to plain text in excel

[SgtBadass]

I've got a column in my spreadsheet in which every item has a hyperlink (I copied it from a website). Is it possible to create another colum that has the plain text version of the hyperlink in it?

 

[firefytr]

Of course.

=A1

If you want static values, copy it then Edit | Paste Special | Values. Or you can right click a cell and choose Remove Hyperlink. Or, my preferred method ...

Select all the cells
Press Alt + F11
Press Ctrl + g
Type this: "Selection.hyperlinks.delete"
then press Enter
Close that window
Voila!

HTH

-----------
Regards,
Zack Barresse

 

[SgtBadass]

I'm afraid none of those worked!

=A1 just gave me the link again in the new cell, copy paste special values did the same and the VB console just left me with the text without the hyperlink, but the hyperlink value had gone!

Perhaps my spreadsheet is formatted in some bizzaire way?

 

[firefytr]

Ah, I see, you want the hyperlink address. Okay, well this will take the selection (you must select all the cells in question) and put the hyperlink addy one column to the right of each cell ...

Sub LeaveFriendlyNamesPlease()
    Dim c As Range
    On Error Resume Next 'for non web-based addresses
    For Each c In Selection
        c.Offset(0, 1).Value = c.Hyperlinks(1).Address
    Next c
End Sub

This needs to go into a Standard Module. So hit Alt + F11 to open the VBE, Ctrl + R to open the Project Explorer, select your project, hit Insert | Module, paste code on right. Put your cursor in the code and press F5 to run.

HTH

-----------
Regards,
Zack Barresse

 

[SgtBadass]

B-r-i-l-l-i-a-n-t-!!!!!

Cheers buddy, I owe you a beer.