Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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" wrote in message ... | | 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. |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove hyperlinks | Excel Discussion (Misc queries) | |||
How to remove all hyperlinks from a worksheet in Excel? | Excel Worksheet Functions | |||
remove hyperlinks | Excel Discussion (Misc queries) | |||
How to change the Excel Title Bar to show the full file path na... | Excel Discussion (Misc queries) | |||
remove all Hyperlinks at one time. | Excel Discussion (Misc queries) |