Forum Discussion
Editing hyperlinks in bulk
I have a speadsheet that has the emails but only as hyperlinks. I just want to make this a plain text doc with the email addresses and don't want to have to click and edit each record individually. Tried a macro to no avail. Thanks
Hi Kim,
Please try this code.
Sub GetHyperlinksAddresses()
'Written By Haytham Amairah
Dim hy As Hyperlink
For Each hy In ActiveSheet.Hyperlinks
hy.Range.Value = hy.Address
hy.Delete
Next hy
End SubRegards
7 Replies
Hi Kim,
Please try this code.
Sub GetHyperlinksAddresses()
'Written By Haytham Amairah
Dim hy As Hyperlink
For Each hy In ActiveSheet.Hyperlinks
hy.Range.Value = hy.Address
hy.Delete
Next hy
End SubRegards
Hi there. I'm wondering if you can help me. I have a similar problem - a file with hundreds of hyperlinks, pointing to the wrong server. Need to edit them all to replace server name. i.e. file:///\\jp2gottnas\Main%20Data\SHARED.....
to
file:///\\jp2gdata\Main%20Data\SHARED.....
It seems that a slight modification to your script might do the trick...
Your help would be MUCH appreciated.
Thank you! You are my hero!
- Yes - that just returns the "text to display" which is "Email Me" and not the address. Thanks tho.
- Ok I get you now. I read that you may have had it the display text as hyperlink so saving as CSV made sense.
I'm with Sergei on this one. No such feature short of some VBA code.
Best wishes Kim.
Cheers
Damien Hi Kim,
That's only with VBA (or third-party tools) to extract the address, not display text. Example is here https://superuser.com/questions/691116/extract-url-from-anchor-text-in-excel and you may google for more similar ones.
Hi Kim
Have you tried saving the Excel doc as a CSV or txt file (File > Save As)?
In either option, it will give you the addresses as plain text without the hyperlinks.
Cheers
Damien
