Can't sum column till I remove apostrophes

[Takoateli]

I’m taking data and pasting it into a calc sheet. A single row looks like this:

$125.00 BsF 273,000.00 BsF 34,125,000.00 Banesco

Column A contains a dollar amount but it’s text formatted with a leading dollar sign. If I eliminate the dollar signs and format column A to currency and then add a column total with a the formula =sum(a2:a96) the result is incorrectly 0. If I inspect a cell in column A it shows a leading apostrophe which doesn’t show when the cell is not being inspected. The process of formatting the column to currency adds the stealth apostrophes. If I manually remove the apostrophes the values in column A are added correctly.

What’s going on? How do I get the column to be formatted as currency without the pesky apostrophes which break formulas?

[mikekaganski]

There is a misconception wrt formatting vs conversion.

Formatting of a cell allows to display its content using some rules. But it does not (and must not) change the type of data stored in the cell. The type of data is decided at the moment of entering the data into the cell; and should stay intact regardless of subsequent changes of formatting. At the moment of data entry, the cell format is taken into account, yes; e.g., if you make input into a cell formatted as text, then no matter what you enter, and if it could be converted to a number/datetime/formula - it will be treated as text. But then, when it has been entered, its type detected, and result is stored - it will be kept intact.

When subsequently you want to change the type of data already stored in cells, you should use Data → Text to Columns... tool, which is specifically suited for this kind of task: to change the type of existing data.

[Brianz]

Thank you Mike. I was able to convert one column at a time “text to columns”, then format the numbers as currency. This eliminated hundreds of pesky apostrophes, and allows calculations to be performed.

[Takoateli]

Thank you Mike. I found out what I was doing wrong. When pasting the data into Calc I needed to (and wasn’t) choosing “detect special numbers”. That fixed it.