tad wrote:... Three concerns...
1. Why did Calc see these numbers as "text" when they were not text...
Probably because they were quoted in the CSV file. A leading single quote is the only way I know of to get Calc to treat values in a CSV file as text.
2. The column could not be reformatted as "numbers." Why?
I assume you mean that changing the cell format didn't solve your problem--right? That's because changing the cell format never changes the type of the value that's stored in the cell. The format can only change the way the value is displayed. If your "SERIAL" values were read as text values, changing the cell format can not change them into numeric values.
Calc guesses a value's type when it is entered. To change the type, you have to enter the value again, and give some different clue as to what the type should be, like adding an apostrophe at the start.
3. I searched and searched for a way to remove the apostrophe so the contents of the cells in that column would once again become numbers. Could not figure out how to do so.
You can't remove it because it's not there. The apostrophe is just a clue that Calc should interpret the entry as text when normally it would interpret it as a number. When the value is stored, Calc sees the apostrophe, stores the value as text, then throws the apostrophe away. When you edit the value, Calc sees that the apostrophe is needed, and puts it back to save you from having to type it. When you finish editing, Calc throws it away again.
It's a confusing way to handle the problem, but it's not entirely Calc's fault: it's been this way since the dawn of spreadsheets.
For the record, Excel 2007 handles this without a hitch.
No doubt. Excel is quite good at doing things without bothering the user, but not infrequently the convenience ends up wrapped around the user's neck because Excel guessed wrong and the blissful user never knew there was an issue.
Do you really mean to add up a column of serial numbers?
You can change numeric text to numeric values using Edit > Find & Replace.
1) Select the cells (or the whole column)
2) Format the cells as some numeric format, or just as "Default" (Format > Default Formatting).
3) Edit > Find & Replace
Search for:
.*
Replace with:
&
Options: Regular expressions: YES; Current selection only: YES
Click "Replace all."
This has the effect of re-entering the values but without the apostrophe.
NOTE: It will also throw out any leading zeros that may be important for other purposes.
Alternatively, you can leave the numbers as text, and add them with a formula something like =SUMPRODUCT(VALUE(C2:C11))
