how do I change a hyperlink to text
 

I have a long column of hyperlinks in an excel file. I need to copy the full
addresses as text into an adjacent column. I can do this line by line thru
Edit Hyperlink Ctrl C / Ctrl V, but this is too tedious. Tried to record my
steps to a macro but macro completely ignored my steps!
Thank you

how do I change a hyperlink to text
 

Hi Mmhoballah,

Try::

'=============
Public Sub TesterB01()
Dim rng As Range
Dim HL As Hyperlink

Set rng = Activesheet.Range("J:J") '<<==== CHANGE

For Each HL In rng.Hyperlinks
HL.Parent.Offset(0, 1).Value = HL.Address

Next HL

End Sub
'<<=============


---
Regards,
Norman



"mmhoballah" wrote in message
...
I have a long column of hyperlinks in an excel file. I need to copy the
full
addresses as text into an adjacent column. I can do this line by line thru
Edit Hyperlink Ctrl C / Ctrl V, but this is too tedious. Tried to record
my
steps to a macro but macro completely ignored my steps!
Thank you

how do I change a hyperlink to text
 

worked like a charm, many thanks

"Norman Jones" wrote:

Hi Mmhoballah,

Try::

'=============
Public Sub TesterB01()
Dim rng As Range
Dim HL As Hyperlink

Set rng = Activesheet.Range("J:J") '<<==== CHANGE

For Each HL In rng.Hyperlinks
HL.Parent.Offset(0, 1).Value = HL.Address

Next HL

End Sub
'<<=============


---
Regards,
Norman



"mmhoballah" wrote in message
...
I have a long column of hyperlinks in an excel file. I need to copy the
full
addresses as text into an adjacent column. I can do this line by line thru
Edit Hyperlink Ctrl C / Ctrl V, but this is too tedious. Tried to record
my
steps to a macro but macro completely ignored my steps!
Thank you

how do I change a hyperlink to text -- And back again !
 

This works great for me too. I'm trying to copy the hyperlinks from one excel
document to another.

Using this I've converted the hyperlinks to filepaths. Now, I want to
convert the column of filepaths back to hyperlinks.

Also, I want to display only the filenames.

Regards
Dylan Dawson
Scotland

"mmhoballah" wrote:

worked like a charm, many thanks

"Norman Jones" wrote:

Hi Mmhoballah,

Try::

'=============
Public Sub TesterB01()
Dim rng As Range
Dim HL As Hyperlink

Set rng = Activesheet.Range("J:J") '<<==== CHANGE

For Each HL In rng.Hyperlinks
HL.Parent.Offset(0, 1).Value = HL.Address

Next HL

End Sub
'<<=============


---
Regards,
Norman



"mmhoballah" wrote in message
...
I have a long column of hyperlinks in an excel file. I need to copy the
full
addresses as text into an adjacent column. I can do this line by line thru
Edit Hyperlink Ctrl C / Ctrl V, but this is too tedious. Tried to record
my
steps to a macro but macro completely ignored my steps!
Thank you