I have a SQL SERVER 2005 database filled with VARCHAR, TEXT, and MONEY data. I export the data via Coldfusion to Excel where the end users like the MONEY values formatted to Number.
Normally not a problem. I used #numericformat(fieldname, ‘,.99’)# which yield expected results for about 30 records. Then the formatting of the money fields became text in excel.
I changed the money values in the record before and after the problem occurs but that yielded no results. Then I noticed in one TEXT formatted column of the record causing the issue had an M-dash (double dash) as its first character. As soon as I removed the M-dash all worked as expect until a user entered a # sign, but that was easy to figure out.
Mdash is a special character often copied and pasted from email or Word. Treat it as you would $, %, # characters.
If pasted into a TEXT field it causes output problems when exporting to excel.
It affects fields other than the Text field where it occurs, especially numeric conversions.
