Excel Find Replace stops working

Question

I have found an issue with Excel the issue exists in 2016 and 2019. If you have a series of columns with spaces in them and want to use find replace to remove them after the first time Excel will no longer find anything that you search for in other columns. This happens when you change what your are using for the find replace options.

So I have a spreadsheet with 2 identical columns, they contain spaces, I use find replace on column A for the space I use ALT-0010 using any other option yields the same results.

I tell Excel to find all spaces and replace them with 34 it finds 64 and replaces them with 64, I go to Column B and bring find/replace back up delete the items in the find replace fields. I enter the same information and it says nothing found.

I recorded a video as it is much easier to see it than type it out?

https://youtu.be/iZhV6v1wgZg

Answer 1

@FredF

According to the video you provided, I had a test.

I could reproduce your issue by following your steps. But I noticed that after I replaced the line breaks with Alt+0010 in the first column, if I used Replace function again, the alt+0010 format (A black point.) would automatically appear in "Find what" box and be reused.

But when you reopen this workbook, the "Find what" and "Replace with" boxes will be cleared, which explains why this issue does not exist after reopening workbooks.

I suggest you put cursor in 'Find what" box and press Delete key to make sure "line break character" is cleared after you replaced the line breaks.

Any updates, please let me know.

---

If an Answer is helpful, please click "Accept Answer" and 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.

Answer 2

Thanks for the additional information I think I tried what you ware suggesting with the same result but will try it tomorrow. I spent most of the day working at remote locations.

Answer 3

I found that I could still replicate the issue when clearing the find replace but not on a 100% repeatable basis. It seems like there is a buffer somewhere that gets corrupt from using control characters. I did create a Macro for my customer to provide a a workaround. I also realized i could do an on screen keyboard so the keystrokes are captured in the video. Right now i have to create a new WSUS server VM so it might be the end of the week before I can revisit this issue.