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:te...@test.com"

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 te...@test.com)...

Please help...

thanks,
jared

[Michael Bednarek]

On Tue, 21 Apr 2009 18:44:34 -0600, Jaredean <sh...@prolook.com> wrote in
microsoft.public.excel:

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

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

[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]

On Wed, 22 Apr 2009 18:04:39 -0600, Jaredean <sh...@prolook.com> wrote in
microsoft.public.excel:

>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.