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?
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.- Ok I tried to use both functions, but I'm completely outside of my wheelhouse and it isn't working for me. It's a little bit more complicated than expected. The data has about 10 different options in the column I'm trying to just shorted the verbiage. Why won't find and replace work for me? Why is a formula a better route than using the function that has worked for me in the past?
- Could you pls upload the file with few entries to check.
