Show actual e-mail address in hyperlink in Excel

J

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
 
R

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
 
R

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
 
J

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
 
J

JimMay

Sorry, but JUST for Column A: enter

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

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
 
J

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
 
J

JimMay

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