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?