Excel Automatically adding a workbook link that don't exist

[BRosenberg]

I am copying a formula from word to excel. For some reason, when I copy and paste, the formula goes from 'Sheet Name'!Y2 to 'Sheet 'https://sharepoint.com/personal/...Name’'!Y2. Each instance where I have a 'Sheet Name'! in the formula, it paste in a way where the 'Sheet shows up but excel thinks I am referencing some workbook that doesn't exist named Name, and is stored in a sharepoint file. The original formula does not contain any links to any workbooks. Has anyone ran into this issue before and know how to fix it?

[#2 · Accepted solution]

@BRosenberg 

This issue arises when Excel misinterprets certain characters or formatting during the copy-paste process, especially when copying formulas from Word to Excel. In your case, Excel is mistakenly adding a hyperlink reference (from SharePoint) instead of correctly interpreting the formula.

Here's how you can address this problem:

Possible Fixes:

1. Paste as Plain Text: (Excel 2016, 2019, 2021, and Excel for Microsoft 365)

• Avoid Pasting Formatting: When pasting from Word, use Paste Special to ensure that only the formula itself is copied, without any unwanted formatting or links.
• Steps:
1. Copy the formula from Word.
2. In Excel, right-click on the target cell where you want to paste the formula.
3. Select Paste Special.
4. Choose Values or Text (depending on the version of Excel you are using), then click OK.
• This should prevent Excel from adding SharePoint links to the formula.

2. Disable Hyperlink Conversion: (Excel 2016, 2019, 2021, and Excel for Microsoft 365)

• Prevent Excel from Automatically Converting Text to Links: Excel may automatically try to detect and convert certain text into hyperlinks, particularly if it sees something resembling a URL.
  1. Go to File > Options.
  2. Select the Proofing tab.
  3. Click on AutoCorrect Options.
  4. Under the AutoFormat As You Type tab, uncheck the box for Internet and network paths with hyperlinks.
  5. Click OK to save the changes.
• Steps:
• This will stop Excel from automatically converting the text to hyperlinks when you paste content into it.

3. Manually Remove Hyperlinks: (2010, 2013, 2016, 2019, 2021, and Excel for Microsoft 365)

If hyperlinks are already present in your formula (or are automatically added during pasting), you can remove them manually:

1. After pasting the formula, select the affected cells.
2. Right-click and choose Remove Hyperlinks.

4. Use Notepad as an Intermediary: (for all versions of Excel)

• To strip any formatting or potential hidden links, you can use Notepad as an intermediary:
1. Copy the formula from Word.
2. Paste the formula into Notepad (this will remove any formatting or hidden links).
3. Copy the formula again from Notepad and paste it into Excel.
• This ensures you're copying plain text, and Excel won’t misinterpret it as a hyperlink or external reference.

5. Check for Hidden Characters in Word:  (Word 2016, 2019, 2021, and Word for Microsoft 365)

• Word might have hidden characters that confuse Excel, such as smart quotes or special apostrophes (‘ vs. ').
• Ensure the quotes and other symbols are standard characters when copying.
• You can replace any smart quotes in Word by going to File > Options > Proofing > AutoCorrect Options, and under the AutoFormat As You Type tab, uncheck "Straight quotes" with “smart quotes.”
• Alternatively, manually replace any special characters in Word before copying.

Conclusion:

By either pasting as plain text or using Notepad to remove any hidden formatting, you can prevent Excel from adding SharePoint links or other workbook references that do not exist. Disabling Excel’s automatic hyperlink detection can also help avoid this issue in the future. The text and the step were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

[#3]

This is super helpful, thank you!