Excel - Convert hyperlink to text format

Ask a question


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.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team