A few words of thanks would be greatly appreciated.

Excel - Convert hyperlink to text format


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?


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.

A few words of thanks would be greatly appreciated.

Ask a question
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.

Published by . Latest update on by aakai1056.

This document, titled "Excel - Convert hyperlink to text format," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).