how do I change a hyperlink to text

[mmhoballah]

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

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
>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

[mmhoballah]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>