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

  1.
    DjiDji59430 Posted messages 4280 Registration date   Status Member Last intervention   718
     
    Hello,

    You copy/paste value

    Best regards
    0
  2. 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
  3. 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
  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