Excel - Convert hyperlink to text format

December 2016



Issue


I work with Excel 2003.My problem is, I have to feed a database with old Excel files. There are thousands of hyperlinks. The hyperlink has been transformed to appear as "1". To get my data into the new database I have to import the name in text format. How to find it automatically?

Solution


With VBA:
  • Open the workbook,
  • Make a copy (save as) and work on the copy!
  • In the copy of the workbook:
  • Press ALT + F11
  • Insert/Module
  • Copy/Paste this code:


Sub AfficheNomCompletLienHypertexte() 
Dim Lign As Long, DrLig As Long 
Dim Col As Byte 
Dim NomDuLien As String 

Col = 1 '==> A adapter : n° de la colonne contenant vos liens hypertextes 
With Sheets("Feuil1") '==> A adapter le nom de la feuille contenant vos liens 
    DrLig = Columns(Col).Find("*", , , , xlByColumns, xlPrevious).Row 
    For Lign = 1 To DrLig 
        If .Cells(Lign, Col).Hyperlinks.Count = 1 Then 
            NomDuLien = .Cells(Lign, Col).Hyperlinks(1).Address 
            .Cells(Lign, Col).Hyperlinks.Delete 
            .Cells(Lign, Col).Clear 
            ActiveSheet.Hyperlinks.Add Anchor:=.Cells(Lign, Col), Address:=NomDuLien, TextToDisplay:=NomDuLien 
        End If 
    Next Lign 
End With 
End Sub


You can adapt this code to your spreadsheet.
Press ALT+F8, select "AfficheNomCompletLienHypertexte" and run.

Thanks to Pikaju for this tip.

Related :

This document entitled « Excel - Convert hyperlink to text format » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.