Excel life hack: How to fix UTF-8 Problems
It is not very difficult to fix this annoying problem, but it took me long enough to find the solution. Excel can have a problem with German characters: ü, ö, ä, etcetera for .csv-files that use the general UTF-8 standard.
German character problem with .csv files
Changing the encoding settings of the file seemed like the easiest way. Unfortunately, this did not work in my case. It took me quite a while to find the solution on Quora. This is more of a work-around than an actual fix, but it works on both Windows and Mac-PCs.
Follow these steps to fix your broken .csv-file:
- Open the csv-file that needs to be fixed in Microsoft Excel
- Click = Save as, and save the file as a .txt file
- Close Excel
- Re-open Excel (don't click on the new text-file to open it)
- Open the .txt file FROM Excel (this is important)
- A dialog box will show up: on the right change the encoding to UTF-8 (see screenshot below)
- Follow the regular steps to create a tab-separated file
- Save this file once again as a .csv-file
Change the encoding to Unicode (UTF-8)
The next time you open the .csv file, all of your characters should look exactly as they should. It might not be the perfect solution, but it works.