My spreadsheet has columns of numeric data that appear to be text, so they don’t compute properly. The “Format>Cells>Number” tool has no effect. It looks like the contents of a blank cell should, at the very least, display “0.00”, but the most I can get is a blank cell. And a blank cell appears to be a text (or character) format. If I directly paste a number into the blank cell, the cell then becomes numeric, but I can’t get the format tool to change an entire column into numeric format. Am I missing something?
Check your cells to make sure they only have numbers in them. If you actually input: “00.00” into the cell then went back and tried to format the hole column, it would not recognize that cell as a number. Somewhere in your column you probably have a space or something other than numbers.
If a cell is EMPTY (nothing in it) , it will not show 0. (i believe)
Thanks for the suggestions. The data is clearly and obviously character, not numbers. It’s plain to see. But the question still is “How do I change the columns to numeric?” LibreOffice doesn’t do anything when instructed to format the cells as numeric. Is there anyway to convince LibreOffice to do this in one or two easy steps? I can manage the change by doing a complicated regular expressions search and replace, but it’s so tedious to have to do it over and over, year after year. For years I’m having to do this, all the while hoping that LO will come up with a simple method, but so far nothing.
To convert an imported column from text to numbers:
- Select the column using the column header or just selecting the data. It doesn’t matter if there’s actual text in some of the column cells, only numbers will be changed in the following step.
- From the format menu (or using right-click and select Format Cells) change the cells to a number format (other than General). For example, Number -1235 or Currency.
- With the same column or data selected, use the Data menu and select Text to Columns.
- In the new window which appears, select the head of the column shown (often it reads Standard). This will darken the column indicating it is selected.
- From the drop-down menu, choose (or rechoose) Standard and press Okay. The numerals will shift from “left-aligned” to “right-align”, an indication the process worked.
Step two is critical in making this work, as the Text to Columns function will only affect fields of the numeral (non-General) type (even though a “text” object occupies the cells). This seems to be relevant to LibreOffice 7.1.8.1 (x64). I don’t recall having to change the column format in earlier versions, but maybe I just forgot that step.
Other minor variations of this might work. I couldn’t get it to ever work if I didn’t first change the column/data format to some numeral format (other than General) before using Text to Columns.
And, if you think Text to Columns is a strange tool… so do I. With little background in spreadsheet usage, the terminology makes little sense. A nice tool would read “Text to Number” since that seems to be the only thing I’ve ever used it for.
It may be used to convert text to number; but as well, it may convert numbers to text; or formulas to values; or one column into several. It is not simply “Text to Number”, even if some use it only for that.
It is a bit incorrect. The cells only need to be not formatted as Text, and any numeric format will do, including e.g. Date and Standard.
1 Like
Sorry to report that the solution does not work with me. I have a column of digits of this type: 3069.0146, 718.1875, 510.388671 etc. They are aligned to the left and there are no single quotes at the beginning. Since a simple sum between any two of them gives #VALUE! , I think they are considered as text. Maybe the dot is the problem. It is meant to be the decimal separator. My Italian locale uses a comma for this.
Please read FAQ that @mikekaganski linked to at beginning, especially Find & Replace with different decimal separator
Solved: I have to change the language settings to English, applying the procedure described by Ferrangie and then change back the language to Italian. Thank you
You could have replaced the points with commas to get the same result. (Edit>FInd&Replace…)
Or simply choose “US English” in the Text to Columns dialog’s column format.
Here’s a way.
Copy the cells that you want to convert to numbers. Typically, the numbers would be preceded by an apostrophe. Paste the numbers in any text editor. Copy from there and paste back into Calc keeping shiftkey pressed (shift-control-V). Now import as you’d usually with any text.