Unable to read exported Excel workbook because of illegal character

After exporting from reporting services, Excel opens the workbook with one of the following errors:

"Replaced Part: /xl/worksheets/sheet1.xml part with XML error. Illegal xml character. Line 1, column 36242."

"Excel found unreadable content in..."

SOLUTION

1. Rename the Excel workbook from *.xlsx to *.zip
2. Within the zip file, open the folder \xl\worksheets and open file sheet1.xml in a text editor, i.e. Notepad++
3. Use Notepad++'s GOTO feature to go to position 36242 and determine the offending character
4. Replace this character in the report's source, i.e. by using the SQL REPLACE function if the source is a plain SQL query
   Note: this is a reactive measure. Each time the error occurs, the character needs to be determined and replaced

For example, these Unicode characters cannot be part of the value in any field in the report:

code: visual representation
26: [SUB]
191: ¿