Ask Your Question

Can't sum column till I remove apostrophes

asked 2018-02-13 19:21:30 +0200

Takoateli gravatar image

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?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-02-14 08:20:54 +0200

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.

edit flag offensive delete link more

answered 2018-02-14 15:16:04 +0200

Takoateli gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-02-13 19:21:30 +0200

Seen: 438 times

Last updated: Feb 14 '18