View original (French)

Convert a Hyperlink to Text

Solved
H10 -  
 H10 -
Hello,

I want to convert a hyperlink into text within an Excel cell.

For example, a hyperlink: file://C:\FY 17\CQ-125.doc

I would like to have the characters from this link displayed as text in an Excel cell.........to "manipulate" later on.

Thank you

4 answers

Answer 1 / 4
DjiDji59430 Posted messages 4278 Registration date   Status Member Last intervention   717
 
Hello,

You copy/paste value

Best regards
0
Answer 2 / 4
H10
 
Thank you DjiDji for the response, but it seems to me that the solution is less simple. I will restate the problem.
I have an Excel cell that contains text (e.g., CQ-125). There is also a hyperlink for this cell.
When I hover over this cell, a comment appears with a path and instructions.
What I want is to retrieve the text that makes up the link (i.e., the text of the path; e.g., file:///C:\NCF\UN\Gestion\CQ-125.doc).
Finally, I would like to copy this text into another cell... in plain text format!
I hope I have been more precise.
Thank you for your feedback.
0
Answer 3 / 4
cs_Le Pivert Posted messages 8437 Status Contributor 730
 
Hello,

in VBA, press Alt F11 to access the editor. Select the relevant sheet on the left and insert this code that will trigger with a right-click on the link: 

Option Explicit Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim lien As String If Range(Target.Address).Hyperlinks.Count > 0 Then lien = Range(Target.Address).Hyperlinks(1).Address ActiveCell.Offset(columnOffset:=3).Value = lien '3 columns further to adapt End If End Sub


--
@+ Le Pivert
0
Answer 4 / 4
H10
 
Thank you, Le Pivert

Here is another solution

Sub extractHL()

Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next

End Sub

Have a good end of the day
0