Jun 18 2018 05:58 PM
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
Jun 18 2018 09:43 PM
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
Jun 19 2018 06:21 AM
Jun 19 2018 06:41 AM
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.
Jun 19 2018 05:32 PM
Jun 20 2018 07:56 PM
SolutionHi 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 Sub
Regards
Jan 06 2022 07:07 AM
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.
Jun 20 2018 07:56 PM
SolutionHi 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 Sub
Regards