SOLVED
Home

Editing hyperlinks in bulk

%3CLINGO-SUB%20id%3D%22lingo-sub-205475%22%20slang%3D%22en-US%22%3EEditing%20hyperlinks%20in%20bulk%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205475%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20speadsheet%20that%20has%20the%20emails%20but%20only%20as%20hyperlinks.%20I%20just%20want%20to%20make%20this%20a%20plain%20text%20doc%20with%20the%20email%20addresses%20and%20don't%20want%20to%20have%20to%20click%20and%20edit%20each%20record%20individually.%20Tried%20a%20macro%20to%20no%20avail.%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-205475%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-206826%22%20slang%3D%22en-US%22%3ERe%3A%20Editing%20hyperlinks%20in%20bulk%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-206826%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you!%20You%20are%20my%20hero!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-206423%22%20slang%3D%22en-US%22%3ERe%3A%20Editing%20hyperlinks%20in%20bulk%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-206423%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Kim%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20code.%3C%2FP%3E%3CPRE%3ESub%20GetHyperlinksAddresses()%3CBR%20%2F%3E%3CBR%20%2F%3E'Written%20By%20Haytham%20Amairah%3CBR%20%2F%3E%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20hy%20As%20Hyperlink%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20For%20Each%20hy%20In%20ActiveSheet.Hyperlinks%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20hy.Range.Value%20%3D%20hy.Address%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20hy.Delete%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Next%20hy%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205941%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Editing%20hyperlinks%20in%20bulk%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205941%22%20slang%3D%22en-US%22%3EOk%20I%20get%20you%20now.%20I%20read%20that%20you%20may%20have%20had%20it%20the%20display%20text%20as%20hyperlink%20so%20saving%20as%20CSV%20made%20sense.%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20with%20Sergei%20on%20this%20one.%20No%20such%20feature%20short%20of%20some%20VBA%20code.%3CBR%20%2F%3E%3CBR%20%2F%3EBest%20wishes%20Kim.%3CBR%20%2F%3E%3CBR%20%2F%3ECheers%3CBR%20%2F%3EDamien%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205645%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Editing%20hyperlinks%20in%20bulk%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205645%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Kim%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20only%20with%20VBA%20(or%20third-party%20tools)%20to%20extract%20the%20address%2C%20not%20display%20text.%20Example%20is%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsuperuser.com%2Fquestions%2F691116%2Fextract-url-from-anchor-text-in-excel%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsuperuser.com%2Fquestions%2F691116%2Fextract-url-from-anchor-text-in-excel%3C%2FA%3E%20and%20you%20may%20google%20for%20more%20similar%20ones.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205631%22%20slang%3D%22en-US%22%3ERE%3A%20Editing%20hyperlinks%20in%20bulk%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205631%22%20slang%3D%22en-US%22%3EYes%20-%20that%20just%20returns%20the%20%22text%20to%20display%22%20which%20is%20%22Email%20Me%22%20and%20not%20the%20address.%20Thanks%20tho.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205510%22%20slang%3D%22en-US%22%3ERe%3A%20Editing%20hyperlinks%20in%20bulk%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205510%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Kim%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20you%20tried%20saving%20the%20%3CSTRONG%3EExcel%20doc%3C%2FSTRONG%3E%20as%20a%20%3CSTRONG%3ECSV%3C%2FSTRONG%3E%20or%20%3CSTRONG%3Etxt%3C%2FSTRONG%3E%20file%20%3CSTRONG%3E(File%20%26gt%3B%20Save%20As)%3C%2FSTRONG%3E%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20either%20option%2C%20it%20will%20give%20you%20the%20addresses%20as%20plain%20text%20without%20the%20hyperlinks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3EDamien%3C%2FP%3E%3C%2FLINGO-BODY%3E
Kim Christopher
New Contributor

I have a speadsheet that has the emails but only as hyperlinks. I just want to make this a plain text doc with the email addresses and don't want to have to click and edit each record individually. Tried a macro to no avail. Thanks

6 Replies

Hi Kim

 

Have you tried saving the Excel doc as a CSV or txt file (File > Save As)?

 

In either option, it will give you the addresses as plain text without the hyperlinks.

 

Cheers

Damien

Yes - that just returns the "text to display" which is "Email Me" and not the address. Thanks tho.

Hi Kim,

 

That's only with VBA (or third-party tools) to extract the address, not display text. Example is here https://superuser.com/questions/691116/extract-url-from-anchor-text-in-excel and you may google for more similar ones.

Ok I get you now. I read that you may have had it the display text as hyperlink so saving as CSV made sense.

I'm with Sergei on this one. No such feature short of some VBA code.

Best wishes Kim.

Cheers
Damien
Highlighted
Solution

Hi Kim,

 

Please try this code.

Sub GetHyperlinksAddresses()

'Written By Haytham Amairah

    Dim hy As Hyperlink
    
    For Each hy In ActiveSheet.Hyperlinks
        hy.Range.Value = hy.Address
        hy.Delete
    Next hy

End Sub

 

Regards

Thank you! You are my hero! 

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies