Special Characters and Exporting to Excel 2007

[S2000]

I have a report that is showing a comments field.
The comments come from several different source systems and are littered with special chars (ASCII 26 for example)
Whenever this is shown in a select from the DB (sql server) or on and HTML, PDF, CSV or Excel 200 report they just render as squares, which is fine.
But, when I try and export to excel 2007 I get a conversion error.

You can try this out yourself by adding a column to any report with the calculation of char(26).

Has anyone else had this issue, or do you know a way around it?

There's a lot of historical data, and a lot of new data coming in so I don't particularly want to try and clean it in the database – I just need to export it to excel 2007 without it crashing.

Thanks in advance.

[jriley]

found this on developerworks....this user had troubles with carriage return and line feeds....

http://www.ibm.com/developerworks/forums/message.jspa?messageID=14195633

summary....

"I couldn't find a system wide setting or a report setting to get around this issue but I did find a report speciific work-around. The special characters causing problems were Carriage Return and Line Feed. By using the following calculation we were able to eliminate the unwanted characters from the field and successfully save to CSV...

Replace( Replace ( TEXT_FIELD, char(13), '<CR>' ), char(10), '<LF>' )

Excel 2000 & 2002 don't have problems handling special characters like CR & LF"

good luck,
joe