How do I convert a LibreOffice Calc column into number format?

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?

FAQ

1 Like

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:

  1. 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.
  2. 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.
  3. With the same column or data selected, use the Data menu and select Text to Columns.
  4. 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.
  5. 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