Reverse Hyperlink - change mailto to text

  • Thread starter Thread starter Jaredean
  • Start date Start date
J

Jaredean

I have a spreadsheet that contains e-mail address, but the sheet shows
them just as:

don johnson email 123 Walnut city state

Well, the "email" is actually a link to the persons e-mail address. If
i choose "Edit Hyperlink" the link shows "mailto:[email protected]"

What I want to do is go through the 1,000's of rows and replace the
word "email" with the actuall e-mail address (not the mailto: part,
but just the (e-mail address removed))...

Please help...

thanks,
jared
 
I have a spreadsheet that contains e-mail address, but the sheet shows
them just as:

don johnson email 123 Walnut city state

Well, the "email" is actually a link to the persons e-mail address. If
i choose "Edit Hyperlink" the link shows "mailto:[email protected]"

What I want to do is go through the 1,000's of rows and replace the
word "email" with the actuall e-mail address (not the mailto: part,
but just the (e-mail address removed))...

Please help...

thanks,
jared
Click to expand...

Select the cellls you want to change and try this code:

Dim rngCell As Range

For Each rngCell In Selection
If rngCell.Hyperlinks.Count = 1 Then
rngCell = Mid(rngCell.Hyperlinks(1).Address, 8)
End If
Next rngCell
 
Thanks for the reply...sorry, my VB skills aren't what they should
be...do i start the VB editor and create a new module and paste this
code in and run it as a macro? i tried that and it didn't show up as
a macro in the list (i turned off the security, etc.)

jared
 
Thanks for the reply...sorry, my VB skills aren't what they should
be...do i start the VB editor and create a new module and paste this
code in and run it as a macro? i tried that and it didn't show up as
a macro in the list (i turned off the security, etc.)

jared
Click to expand...

What you did sounds pretty good to me, except that you need to give the
code a name. Put the line
Sub Hyper1()
above the code and the line
End Sub
below it. It should then show up under that name in the list of macros,
or you can run it from the VB editor by pressing F5 when the cursor is
somewhere in the code and you have selected the cells to convert in the
worksheet.
 
Back
Top