We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Can't sum column till I remove apostrophes [closed]

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

Takoateli gravatar image

updated 2020-09-23 13:26:55 +0200

Alex Kemp 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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-19 15:21:37.816681

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


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.

Brianz gravatar imageBrianz ( 2021-05-07 07:34:21 +0200 )edit

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

Question Tools

1 follower


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

Seen: 1,208 times

Last updated: Feb 14 '18