formating a cell to currency, adds an unformattable character to front of cell value. How do I correct this.

I downloaded a spread sheet from fidelity, and the values are interpreted as text. I want to sum the column, so I highlighted the entire column, and applied the currency formatting. The value within the cell still remains as it was, and the value has a ’ character at the start of it.

What am I doing wrong? Or is this a bug?

for instance the value in the cell is $1139.33 and is formatted as text. Trying to change it to currency, the number does not change in appearance (nor shifted to be left aligned) but in the formula bar, the number appears as '$1139.33

It is not able to be added to a formula for summing unless the leading ’ is removed.

What’s with that?

See this FAQ.