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?
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.
3 Likes
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
1 Like
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.
1 Like
Ferrangie’s 5-step solution didn’t work at all for me… because I didn’t follow it closely enough. With a simple CSV of $50.00, $1,200, etc. amounts, in step 4 in the Data > Text to Columns dialog, the column type already shown as “Standard”. But if you don’t select the column and re-choose Standard, the conversion to numbers doesn’t work. I should have read more carefully 
.
This is with Version 26.2.4.2 (x86_64)
As an occasional user, trying to remember the magic to do this is the single most frustrating issue I have with LO Calc.
You may find life less frustrating if you ensure that Detect special numbers is checked when importing CSV files.
1 Like
It helps if you understand the distinction of format vs data type. A slightly simplified overview which works for everyday use:
-
Data type is the manner in which the content of a cell is stored.
For Calc there are essentially two data types you can enter:- number
- text
-
Format is the combination of settings which govern how the stored data should be rendered (the visual output, for humans to interpret).
- Some format settings apply only to the display of data type number, some apply to text data, and some apply to both data types.
- Applying a format does not alter the data type of existing cell content. Only the way it is rendered.
- Some format settings govern how subsequent input is interpreted.
Pasting is a form of manual input. This is why a quick copy/paste after reformatting works.
When you are used to the way MS Excel works, the differences can be frustrating. Excel “helps” by circumventing some problems. This help will sometimes avoid/solve potential issues. Sometimes it will also introduce other issues.
You may find life less frustrating if you ensure that Detect special numbers is checked when importing CSV files.
I retried, and it did help! The preview in the Text Import dialog looks the same (left aligned), but the spreadsheet treats them as numbers and formats the column as currency 
.
Another wrinkle is this particular CSV had numbers in quotes like `"$100.00", probably so amounts like “$1,200” are not treated as two items separated by a comma. So I had to disable “Format quoted field as text” as well as enable “Detect special numbers”. Fortunately the CSV only had a few large amounts with comma digit separators.
It would be nice if the alignment of the amount column changed in the preview in the dialog while you fiddle with the settings, since this is so hard to get right.
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.
This is not a matter of formatting. All number formats display numbers in various ways without changing a number. If your numbers are text, no number formatting applies. No formatting attribute applies any changes to your data.
When importing text from clipboard or from tabular text files (e.g. csv), always check “Detect special numbers” and make sure that the chosen locale matches the locale of the data to be imported. Choose an English locale when importing decimal numbers with points as decimal separator. Choose “English (USA)” when a date string like “01/02/2026” refers to the second of January. Choose “English (UK)” when the same date string “01/02/2026” refers to the first of February.
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.
1 Like
Not in this particular case where the numbers have a wrong decimal separator (point) for the locale in use (Russian).