Forum Discussion
Spreadsheet with hundreds of Hyperlinks
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 int...
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)
Suppose cell A1 contains:
c:\users\jkp\documents\book1.xlsx
then this formula will give a link to that file:
=HYPERLINK("file://"&A1)
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.