Home

Disappearing Hyperlink URL

%3CLINGO-SUB%20id%3D%22lingo-sub-747731%22%20slang%3D%22en-US%22%3EDisappearing%20Hyperlink%20URL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-747731%22%20slang%3D%22en-US%22%3E%3CP%3EI%20got%20a%20file%20with%202000%20hyperlinks.%20The%20thing%20is%20I%20don't%20need%20these%20hyperlinks%20but%20the%20URL.%20I%20need%20to%20convert%20these%20hyperlinks%20to%20URL.%20I%20need%20a%20way%20to%20this%20fast%20and%20I%20need%20to%20now%20how.%20Can%20someone%20help%20me%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-747731%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-747844%22%20slang%3D%22en-US%22%3ERe%3A%20Disappearing%20Hyperlink%20URL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-747844%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F374205%22%20target%3D%22_blank%22%3E%40Adrie0181%3C%2FA%3EYou%20can%20use%20a%20macro%20like%20this%20one.%20Make%20sure%20you%20run%20this%20against%20a%20copy%20of%20your%20file%20as%20this%20macro%20cannot%20be%20undone.%3C%2FP%3E%0A%3CPRE%3EOption%20Explicit%0A%0ASub%20ReplaceHyperlinks()%0A%20%20%20%20Dim%20aCell%20As%20Range%0A%20%20%20%20For%20Each%20aCell%20In%20ActiveSheet.UsedRange%0A%20%20%20%20%20%20%20%20If%20aCell.Hyperlinks.Count%20%26gt%3B%200%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20aCell.Value%20%3D%20aCell.Hyperlinks(1).Address%0A%20%20%20%20%20%20%20%20%20%20%20%20aCell.Hyperlinks.Delete%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%0AEnd%20Sub%0A%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Adrie0181
Occasional Visitor

I got a file with 2000 hyperlinks. The thing is I don't need these hyperlinks but the URL. I need to convert these hyperlinks to URL. I need a way to this fast and I need to now how. Can someone help me?

1 Reply
Highlighted

@Adrie0181 You can use a macro like this one. Make sure you run this against a copy of your file as this macro cannot be undone.

Option Explicit

Sub ReplaceHyperlinks()
    Dim aCell As Range
    For Each aCell In ActiveSheet.UsedRange
        If aCell.Hyperlinks.Count > 0 Then
            aCell.Value = aCell.Hyperlinks(1).Address
            aCell.Hyperlinks.Delete
        End If
    Next
End Sub
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies