Forum Discussion
Find & Replace Issues
The find and replace function is telling me it can't anything to replace. Why can it not find the data that is there?
6 Replies
Just a guess without seeing the sheet but did you clear the 'Find format' from a previous search?
- Hello sgravois, Oh that question is so wide and the answer is so all over the place that it can't be answered in a way that you like it. OK let me explain A1 has "HELLO" and B1 has "Hello " you see that space at the end. These, to the machine, are two different strings. Now imagine all these keys on your keyboard and all the non-printable characters that can be produced by that keyboard. (https://www.techopedia.com/definition/29785/non-printable-characters#:~:text=Non-printable%20characters%20are%20used%20to%20indicate%20certain%20formatting,Line%20breaks%205%20Page%20breaks%206%20Null%20characters) So, what we need to do is address the data quality and make sure MS-Excel has what it needs. To make cell A1 and B1 be equal we can do something like =CLEAN(TRIM(A1))=CLEAN(TRIM(B1)) and that would be true. The CLEAN and TRIM functions clean up MOST but not ALL non-printable characters. Try it and see how it works.
Ok so I don't think "dirty" hehe data is the cause. the information in the cells was generated by Qualtrics a survey program our university provides. so the information was exported to excel and I used the expanded options in Find and Replace to identify the exact format I was seeking to change and edited one entry to use it as the other format I wanted to change it to, but it still says it can't find the information. I'm basically trying to change Junior BB to Jr. BB and I'm struggling.
- Hi sgravois,
"information in the cells was generated by Qualtrics" I had a hunch and I was on the right track.
OK try using the SUBSTITUTE() and/or REPLACE() functions and see if that helps.
