I heavily use a vba program called TrimAllCells that nicely cleans up any spreadsheet.
Of course, the vba routine is supposed to leave the worksheet formatting intact.
The routine cannot seem to delete leading apostrophe's in Excel 2010.
The only workaround I have found is to clear all formats which my vba routine should NOT do.
The following vba code demonstrates the problem.
.formula = "'test" ' this turns on prefixcharacter. I can't figure out how to turn it off
.Value = "test" ' I think this worked in Excel 2003, but definitely does not in 2010
MsgBox .PrefixCharacter & .formula
p.s. by the way, this is not just a vba problem. The same thing happens manually -- once you insert a leading apostrophe, it cannot be removed except by clearing the formats.