Hyperlinks keep changing after cut/copy - paste

[DonBici]

Hi! I have an Excel file with my CD collection. In that file I have several columns with hyperlinks and each hyperlink points to a particular page on discogs.com (with all the information of that CD), so they are all https://..., pages, I'm not talking about links to other tabs in the stylesheet. When I add a new CD, I have to cut the data in several cells in multiple columns (like number, title, label number, discogs link and price) and rows and paste them a row lower. When I do that, the hyperlinks all look fine, but somehow, the pages the hyperlinks should be pointing to, have changed! And this "glitch" is completely random: For instance, I just found a hyperlink in G111 (albums) that points to a discogs page that should be pointed to from a link in AI28 (CD singles, which is a completely different column and should not be affected by cut and paste actions in other columns).  So the hyperlink reads https://www.discogs.com/release/1410441-Cure-Wish, but when you hover over it it says https://www.discogs.com/release/1481440-The-Cure-Lovesong.

The only way I know how to fix every link individually, is to place the cursor in front of the link, hit backspace and then ENTER. After doing this, the hyperlink points to the correct page again. But I have hundreds of CDs and it's quite annoying to have to correct the hyperlink every single time I've added a new CD!

Why is this issue occurring and how can I solve it once and for all?

[#2 · Accepted solution]

@DonBici 

This issue with hyperlinks changing after cut and paste in Excel is a known problem and can be frustrating, especially when dealing with large datasets. Here's why it happens and how you can potentially solve it:

Why it happens:

When you cut and paste cells containing hyperlinks, Excel sometimes adjusts the relative paths based on the new location of the pasted cells. This can lead to incorrect links pointing to different pages than intended.

Potential solutions:

1. Paste using Paste Special:

Instead of using the regular "Paste" command, try using "Paste Special" and choose the option "Values and Hyperlinks." This option pastes the cell values and preserves the original hyperlink addresses without any adjustments.

2. Use absolute references:

You can modify your hyperlinks to use absolute references instead of relative ones. Absolute references point to specific locations regardless of the cell's position in the spreadsheet. To create an absolute reference:

• Before the link: Add a dollar sign ($) before both the column and row number in the hyperlink address. For example, instead of https://www.discogs.com/release/1410441-Cure-Wish, use https://www.discogs.com/release/$A$1410441-Cure-Wish.
• Within the link: If the link contains internal references to other cells within your spreadsheet, use absolute references for those cells as well.

3. Use helper columns:

Consider using a separate column to store the absolute hyperlink addresses. This way, you can copy the values from this column and paste them into the hyperlink field without worrying about relative path adjustments.

4. Use VBA macros (advanced users):

If you're comfortable with VBA, you can create a macro that automatically corrects hyperlinks after cut and paste operations. This requires writing code to identify and adjust the links based on specific criteria.

Additional tips:

• Save frequently: This ensures you don't lose significant work if the issue persists and requires manual correction.
• Test your solutions: After implementing any of these methods, test a few links to ensure they work as expected before applying them to your entire dataset.

By trying these solutions, you should be able to minimize the issue of hyperlinks changing after cut and paste in your Excel spreadsheet. Remember, the most suitable approach might depend on your specific workflow and preferences

[#3]

@smylbugti222gmailcomThank you so much for your elaborate reply! I'll give your suggestions a go! Have a great day.