Macro for removing friendly names

[SR1]

Hello,

I need a macro that will replace the friendly of hyperlinked text with just the URL in a selected range of cells (can be either single or multiple selection).

(eg BBC news website as just http://news.bbc.co.uk)

The spreadsheet contains nearly 200 rows and this would obviously save a lot of time.

Thanks if you could help!

 

[xld]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A"    '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim cell As Range

    With Application
        
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    With ActiveSheet
        
        LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
        For i = 1 To LastRow
        
            .Cells(i, "A").Value = .Cells(i, "A").Hyperlinks(1).Address
        Next i
    End With
    
    With Application
        
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
    
End Sub

 

[Lewiy]

Try this.....select the range of cells and run the macro:

Sub RemoveFriendlies()
Dim iRange As Range, r As Range
Set iRange = Selection
For Each r In iRange
    With r.Hyperlinks(1)
        .TextToDisplay = .Address
    End With
Next r
End Sub