Use an advanced filter to filter and paste the results as values into a new worksheet in one go which at least saves a step and is probably more reproducable in fewer clicks every time after the first. You can also select only the columns you want and in any order.
Debra's site is probably the best resource for examples on this:
[www.contextures.com][1]
[1]: http:// www.contextures.com
There are no polite answers to questions of the form "Why does Excel do X?".
Why don't you just adopt "Save as Unicode Text" as your standard transfer format? Excel Save as ensures there's a BOM, uses TAB as a delimiter, quotes fields just like CSV ...
By the way, you don't "select the CSV extension". You have TWO choices -- format and extension. Try save as unicode text in the 'Save as type' box and manually change the extension in the 'File name' box to csv. Then exit from Excel and double-click on the file name in Windows Explorer. Opens up fine. Try it.
Update to discuss OP's comments:
- "Save as Unicode Text" (in Excel 2007) will always save your file as a Text extension (I can't speak for previous or newer versions). That's the idea of "Save as type"...
=> Please read carefully what I wrote above and try it out for your self. It will always save your file with a TXT extension unless you override that: delete the txt in the 'File name' box and type csv.
- To clarify your "Excel Save as ensure there's a BOM", is incorrect.
=> "Save as Unicode text" ensures that there is a UTF-16LE BOM at the front of the file. Try it and see.
- This is the reason I asked a question in the first place! If I open a CSV formatted file that contains a BOM to indicate my file Encoding is UFT-16LE and modify this file via Excel, I expect when I save this file that the Encoding remains in tact...
=> and your expectations are not met, because Excel doesn't retain any info about input CSV files. There is no way to force it to do what you want to do. So you either give up or try something else, like what I'm suggesting.
- What would be really nice is someone who's experienced these problems before, provide your insight please.
=> I have experienced these problems, with Chinese data and Excel 1997, years ago. I have, before answering your question, verified with Excel 2003 and Excel 2007 that Excel is still behaving badly. I have verified that the "save as type = Unicode text, extension = CSV" workaround does indeed work. I have provided my insights.
Best Answer
In this question, you will find Excel indeed does not preserve unicode characters when saving as csv. Their solution is to save as unicode tab separated text
To save with Unicode encoding in Excel,
Save as, choose text, then next to the save button, you see "tools" menu. Choose web options, then encoding tab, then select the encoding you like. I would suggest to save everything in UTF8, but that is your choice. Note this is in Excel 2007 or above.