Remove Hyperlinks...show the Path

If you would like to permanently display the path in the
cell (i.e. replace microsoft with www.microsoft.com), right click on the
cell, and use edit hyperlinks. The top pane shows the display name, the
bottom pane shows the path. Copy the path to the display name. I am not
sure if there is a quick way to do this. NOTE: You will have to use keyboard
commands to copy and paste.

This solution works, but it is one by one. Does anyone k now of a mass
change method? I want to take a hyperlinked word (web address or email
address) and have it display the hyperlink properties instead of the word.

If you just want to change the text displayed in the cell:

Dim Cell As Range
For Each Cell In Selection
Cell.Value = "www." & Cell.Value & ".com"
Next


--
Jim
"BenJAMMIN" <[email protected]> wrote in message
news:[email protected]...
|
| If you would like to permanently display the path in the
| cell (i.e. replace microsoft with www.microsoft.com), right click on the
| cell, and use edit hyperlinks. The top pane shows the display name, the
| bottom pane shows the path. Copy the path to the display name. I am not
| sure if there is a quick way to do this. NOTE: You will have to use
keyboard
| commands to copy and paste.
|
| This solution works, but it is one by one. Does anyone k now of a mass
| change method? I want to take a hyperlinked word (web address or email
| address) and have it display the hyperlink properties instead of the word.

This might get you started:

Option Explicit
Sub testme()

Dim myHyperlink As Hyperlink
Dim wks As Worksheet

Set wks = ActiveSheet

For Each myHyperlink In wks.Hyperlinks
myHyperlink.Parent.Value = myHyperlink.Address
Next myHyperlink

End Sub

But my tests returned:
http://www.microsoft.com/

If that's a problem, you could eliminate the http:// and trailing slash if you
want.

Option Explicit
Sub testme()

Dim myHyperlink As Hyperlink
Dim wks As Worksheet
Dim myStr As String
Dim SlashPos As Long

Set wks = ActiveSheet

For Each myHyperlink In wks.Hyperlinks
myStr = myHyperlink.Address
SlashPos = InStr(1, myStr, "//")
If SlashPos > 0 Then
myStr = Mid(myStr, SlashPos + 2)
End If
If LCase(Left(myStr, 7)) = "mailto:" Then
myStr = Mid(myStr, 8)
End If
If Right(myStr, 1) = "/" Then
myStr = Left(myStr, Len(myStr) - 1)
End If
myHyperlink.Parent.Value = myStr
Next myHyperlink

End Sub


BenJAMMIN wrote:
>
> If you would like to permanently display the path in the
> cell (i.e. replace microsoft with www.microsoft.com), right click on the
> cell, and use edit hyperlinks. The top pane shows the display name, the
> bottom pane shows the path. Copy the path to the display name. I am not
> sure if there is a quick way to do this. NOTE: You will have to use keyboard
> commands to copy and paste.
>
> This solution works, but it is one by one. Does anyone k now of a mass
> change method? I want to take a hyperlinked word (web address or email
> address) and have it display the hyperlink properties instead of the word.

--

Dave Peterson