Show actual e-mail address in hyperlink in Excel

[Jens]

I have spend many hours searching for an easy way of changing a columb
where the visible text is "e-mail" and it's a hyperling to some e-mail
addresses, that I can see if I move the curser over the cells. I have
found some solutions concerning macros but as I'm not used to working
with macros, this seems very difficult for me!
Anybody have an idea?
Maybe I need to buy a small programme, or is it free ware?
br/Jens

[Ron de Bruin]

Try this one with the e-mail links in column A
It show you the mail address in column B

Sub test()
Dim hlnk As Hyperlink
For Each hlnk In ActiveSheet.Hyperlinks
hlnk.Parent.Offset(0, 1).Value = Right(hlnk.Address, Len(hlnk.Address) - 7)
Next
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jens" <[email protected]> wrote in message news:[email protected]...
>I have spend many hours searching for an easy way of changing a columb
> where the visible text is "e-mail" and it's a hyperling to some e-mail
> addresses, that I can see if I move the curser over the cells. I have
> found some solutions concerning macros but as I'm not used to working
> with macros, this seems very difficult for me!
> Anybody have an idea?
> Maybe I need to buy a small programme, or is it free ware?
> br/Jens
>

[Ron de Bruin]

Oops

This one look only in column A

Sub test()
Dim hlnk As Hyperlink
For Each hlnk In Columns("A").Hyperlinks
hlnk.Parent.Offset(0, 1).Value = Right(hlnk.Address, Len(hlnk.Address) - 7)
Next
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" <[email protected]> wrote in message news:urmK%[email protected]...
> Try this one with the e-mail links in column A
> It show you the mail address in column B
>
> Sub test()
> Dim hlnk As Hyperlink
> For Each hlnk In ActiveSheet.Hyperlinks
> hlnk.Parent.Offset(0, 1).Value = Right(hlnk.Address, Len(hlnk.Address) - 7)
> Next
> End Sub
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Jens" <[email protected]> wrote in message news:[email protected]...
>>I have spend many hours searching for an easy way of changing a columb
>> where the visible text is "e-mail" and it's a hyperling to some e-mail
>> addresses, that I can see if I move the curser over the cells. I have
>> found some solutions concerning macros but as I'm not used to working
>> with macros, this seems very difficult for me!
>> Anybody have an idea?
>> Maybe I need to buy a small programme, or is it free ware?
>> br/Jens
>>
>
>

[JimMay]

Back up your file before attempting this, but

With your sheet - the active sheet - Press Alt+F11 (the function key)
This will open the VBE Environment window - at the menu Veiw, select
Immediate Window, type in:

Application.Activesheet.hyperlinks.delete

And press the enter key.

All your hyperlinks should disappear.

HTH
Jim May


"Jens" <[email protected]> wrote in message
news:[email protected]:

> I have spend many hours searching for an easy way of changing a columb
> where the visible text is "e-mail" and it's a hyperling to some e-mail
> addresses, that I can see if I move the curser over the cells. I have
> found some solutions concerning macros but as I'm not used to working
> with macros, this seems very difficult for me!
> Anybody have an idea?
> Maybe I need to buy a small programme, or is it free ware?
> br/Jens

[JimMay]

Sorry, but JUST for Column A: enter

Application.Activesheet.columns(1).hyperlinks.delete

"JimMay" <[email protected]> wrote in message
news:3Rheg.33841$fG3.15567@dukeread09:

> Back up your file before attempting this, but
>
> With your sheet - the active sheet - Press Alt+F11 (the function key)
> This will open the VBE Environment window - at the menu Veiw, select
> Immediate Window, type in:
>
> Application.Activesheet.hyperlinks.delete
>
> And press the enter key.
>
> All your hyperlinks should disappear.
>
> HTH
> Jim May
>
>
> "Jens" <[email protected]> wrote in message
> news:[email protected]:
>
> > I have spend many hours searching for an easy way of changing a columb
> > where the visible text is "e-mail" and it's a hyperling to some e-mail
> > addresses, that I can see if I move the curser over the cells. I have
> > found some solutions concerning macros but as I'm not used to working
> > with macros, this seems very difficult for me!
> > Anybody have an idea?
> > Maybe I need to buy a small programme, or is it free ware?
> > br/Jens

[Jens]

Hi Jim,
Yes it works, but it only removes the hyperlinks (and I actually still
want them) - I want to see the actual addresses - now all that I see is
the text that was there also before "E-MAIL"
br/Jens

[Jens]

Hi Ron,
Thanks!
I have never actually worked in VBE, but just tried my way, adn puff
the magic was doen and I have opened all the addresses!
Thanks again!
br/Jens

[Ron de Bruin]

Try my example Jens

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jens" <[email protected]> wrote in message news:[email protected]...
> Hi Jim,
> Yes it works, but it only removes the hyperlinks (and I actually still
> want them) - I want to see the actual addresses - now all that I see is
> the text that was there also before "E-MAIL"
> br/Jens
>

[JimMay]

Jens;
Obviously, I misunderstood your request.
It would be best if you follow Ron's suggestion.
Jim

"Jens" <[email protected]> wrote in message
news:[email protected]:

> Hi Jim,
> Yes it works, but it only removes the hyperlinks (and I actually still
> want them) - I want to see the actual addresses - now all that I see is
> the text that was there also before "E-MAIL"
> br/Jens