Reverse Hyperlink - change mailto to text

[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

 

[Michael Bednarek]

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

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

 

[Jaredean]

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

 

[Michael Bednarek]

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

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.