Home

Spreadsheet with hundreds of Hyperlinks

%3CLINGO-SUB%20id%3D%22lingo-sub-731354%22%20slang%3D%22en-US%22%3ESpreadsheet%20with%20hundreds%20of%20Hyperlinks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-731354%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20been%20working%20with%20EXCEL%20365%202016%20on%20Windows%2010.%20I%20have%20spreadsheet%20with%20hundreds%20of%20hyperlinks%20and%20am%20having%20trouble%20sorting%2C%20moving%2C%20cutting%20and%20pasting%20information.%20the%20file%20usually%20goes%20into%20'not%20responding'%20and%20hangs%20up.%20I%20store%20the%20data%20on%20my%20SSD%20along%20with%20the%20EXCEL%20app.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20some%20type%20of%20limitations%20with%20excel%20and%20hyperlinks%3F%20I%20try%20to%20sort%20on%20the%20column%20that%20has%20hyperlinks%20and%20sometimes%20it%20works%20and%20sometimes%20it%20does%20not%20sort%20accurately.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20basically%20had%20the%20same%20problem%20when%20I%20was%20using%20EXCEL%202010.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-731354%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-731613%22%20slang%3D%22en-US%22%3ERe%3A%20Spreadsheet%20with%20hundreds%20of%20Hyperlinks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-731613%22%20slang%3D%22en-US%22%3EHyperlinks%20do%20have%20their%20disadvantages%20as%20you've%20discovered.%20I%20susally%20prefer%20to%20place%20the%20link%20address%20as%20static%20text%20into%20a%20cell%20and%20then%20use%20the%20HYPERLINK%20worksheet%20function%20to%20generate%20the%20link.%20Depending%20on%20the%20type%20of%20link%20you%20sometimes%20need%20a%20prefix%20in%20front%20of%20the%20address%2C%20like%20%22File%3A%2F%2F%22%20for%20links%20to%20a%20file%2C%20or%20a%20simple%20%22%23%22%20for%20links%20to%20cells%20on%20a%20worksheet.%20Example%3A%3CBR%20%2F%3ESuppose%20cell%20A1%20contains%3A%3CBR%20%2F%3Ec%3A%5Cusers%5Cjkp%5Cdocuments%5Cbook1.xlsx%3CBR%20%2F%3Ethen%20this%20formula%20will%20give%20a%20link%20to%20that%20file%3A%3CBR%20%2F%3E%3DHYPERLINK(%22file%3A%2F%2F%22%26amp%3BA1)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-731916%22%20slang%3D%22en-US%22%3ERe%3A%20Spreadsheet%20with%20hundreds%20of%20Hyperlinks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-731916%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20small%20sample%20of%20what%20I%20have.%20The%20original%20spreadsheet%20has%20over%205000%20rows%20with%20numerous%20hyperlinks.%20The%20Hyperlinks%20are%20to%20a%20website%20called%20'findagrave.com'%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20plan%20on%20importing%20them%20into%20ACCESS%20when%20I%20have%20completed%20updating%20each%20row.%20That%20is%20I%20follow%20each%20link.%20look%20up%20and%20copy%20information%20from%20that%20site%20and%20copy%20them%20into%20my%20spreadsheet.%20When%20importing%20them%20into%20ACCESS%20some%20of%20the%20links%20worked%20and%20some%20didn't%20even%20tho'%20i%20used%20the%20field%20as%20HTML.%20The%20file%20was%20so%20big%20that%20I%20created%20different%20spreadsheet%20for%20each%20state%2C%20but%20still%20have%20the%20same%20issues.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20could%20delete%20the%20hyperlinks%20when%20I%20am%20done%20editing%20but%20until%20then%20I%20need%20them.%26nbsp%3B%20I%20thought%20there%20may%20be%20a%20quick%20fix%20but%20maybe%20not.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-733617%22%20slang%3D%22en-US%22%3ERe%3A%20Spreadsheet%20with%20hundreds%20of%20Hyperlinks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-733617%22%20slang%3D%22en-US%22%3EGiven%20that%20the%20link%20URLs%20are%20in%20column%20AW%20and%20the%20link%20text%20is%20in%20column%20D%20you%20could%20remove%20all%20hyperlinks%20from%20column%20D%20(select%20all%20of%20column%20D%2C%20and%20on%20the%20Home%20tab%20click%20the%20eraser%20icon%20and%20choose%20Clear%20Hyperlinks).%20Then%20use%20any%20column%20you%20like%20and%20insert%20this%20formula%3A%3CBR%20%2F%3E%3DHYPERLINK(D2%2CAW2)%3CBR%20%2F%3EI%20expect%20sorting%20and%20filtering%20should%20now%20work%20as%20it%20should.%3C%2FLINGO-BODY%3E
steveashbaugh1946
New Contributor

I have been working with EXCEL 365 2016 on Windows 10. I have spreadsheet with hundreds of hyperlinks and am having trouble sorting, moving, cutting and pasting information. the file usually goes into 'not responding' and hangs up. I store the data on my SSD along with the EXCEL app. 

 

Is there some type of limitations with excel and hyperlinks? I try to sort on the column that has hyperlinks and sometimes it works and sometimes it does not sort accurately.

 

I basically had the same problem when I was using EXCEL 2010.

 

Thank you

3 Replies
Hyperlinks do have their disadvantages as you've discovered. I susally prefer to place the link address as static text into a cell and then use the HYPERLINK worksheet function to generate the link. Depending on the type of link you sometimes need a prefix in front of the address, like "File://" for links to a file, or a simple "#" for links to cells on a worksheet. Example:
Suppose cell A1 contains:
c:\users\jkp\documents\book1.xlsx
then this formula will give a link to that file:
=HYPERLINK("file://"&A1)

@Jan Karel Pieterse 

Here is a small sample of what I have. The original spreadsheet has over 5000 rows with numerous hyperlinks. The Hyperlinks are to a website called 'findagrave.com' 

 

I plan on importing them into ACCESS when I have completed updating each row. That is I follow each link. look up and copy information from that site and copy them into my spreadsheet. When importing them into ACCESS some of the links worked and some didn't even tho' i used the field as HTML. The file was so big that I created different spreadsheet for each state, but still have the same issues.

 

I could delete the hyperlinks when I am done editing but until then I need them.  I thought there may be a quick fix but maybe not.

 

Thanks for your time.

Given that the link URLs are in column AW and the link text is in column D you could remove all hyperlinks from column D (select all of column D, and on the Home tab click the eraser icon and choose Clear Hyperlinks). Then use any column you like and insert this formula:
=HYPERLINK(D2,AW2)
I expect sorting and filtering should now work as it should.
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies