Hello. I am having an issue with text. I am using find/replace in a macro to clean some data but have hit a wall.
Some of the data I have looks like this:
"blahblahblah
or
blahblahblah"
or
blahblahblah "
or
"blahblahblah"
etc...
I want just plain blahblahblah.
I copied this data from another source. I have tried clearing the formats, changing formats. Please Help!!
Have you tried to do just a simple find/replace where the value you want to find is " and the value you want to replace it with is blank? This is usually what I use when I want to delete a bunch of recurring "junk" data.
Find & Replace (Ctrl +H)
Find: "
Replace:
Life's a spreadsheet, Excel!
Say thanks, Click *
This is still not working, as the quotations only show up when the text is copied and pasted into a non-cell.
When looking at the cell all seems normal. The text used to be wrapped, but has been un-wrapped. Maybe that has something to do with it?
I have attatched an example
You can use Application.Clean() to clean the common non-printable characters. From experience that may not be enough and you have to create a loop with all the non-letter, non-number characters.
abousetta
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
Thank you very much!!
Just as a side note, you can get the same results by using the formula Clean()
Application.clean() and WorksheetFunction.Clean() are the same thing
I just checked, it is a non-printable character that is cleaned using the worksheet function Clean. If you want to use it in vba then you have to add either Application or WorksheetFunction before it.
abousetta
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks