Cannot paste into Excel "Find and Replace"

Question

Hi, there! I have a user who has run into a very frustrating issue with not being able to paste data from one sheet in a workbook into "Find and Replace" so he can find things in another sheet in the workbook. It only seems to be happening with this one workbook. He's been working on it for months, worked on it just fine Friday last week, then he came in Monday (yesterday) and it was acting like this. Worth noting that this workbook was created by someone else two years ago and he got access to it a few months ago.

What we've tried:
-Pasting by right-clicking, pasting with Ctrl V, pasting from the Clipboard directly
-Rebooting the PC
-Repairing Excel
-Copying and pasting the data into Notepad and then copying from there (still can't paste that information in; when we did this, we found there was a bunch of spaces that came with the text that got pasted into the Notepad note)
-Unblocking in properties
-Opening and trying on a different machine (still can't paste)
-Opening it while running Safe Mode (still can't paste)

Another symptom he's noticed: usually when he opens this workbook, he'll get the yellow notification bar with the message, "Security Warning: Macros have been disabled" with the button labeled, "Enable Content," but he hasn't been receiving that message yesterday and today. This workbook does not have any macros when we look through all the macro settings. Might be unrelated, but thought I'd mention it.

Answer 1

Hi @KJ,

Thank you for your detailed information regarding the situation.

Based on your sharing, this is such a strange behavior since if the user is able to select the Find What field in "Find and Replace", he should be able to paste or add data there to do a search. However, you mentioned that Notepad shows "lots of spaces that came with the text", I suspect this is highly the culprit of the issue.

In Excel, Find & Replace and other search-related functions have an input limitation at 255 characters. If the data in that cell has lots of spaces when pasting into Notepad, it can inflate the length to exceed 255 characters, and Excel won't allow pasting it into Find & Replace. Combining with the details that it fails on another machine and Safe Mode, it helps proving the issue is the workbook content itself.

You can ask that user checking the length of that cell by using the formula =LEN(cell). If the result is larger than 255, that's the problem. Then you can use this formula to clean up the data: =TRIM(cell) to remove all unnecessary spaces inside that data cell.

Please understand that my initial response does not always resolve the issue immediately. However, you can try checking and let me know if it works for you. If not, we can work together to find a solution.

If you have any questions or need further assistance, please feel free to share them in the comments on this post so I can continue to support you.    

Thank you for your understanding and cooperation. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread