I working in Libreoffice in Ubuntu. I have a text file that has some columns of text and columns of numbers. When I import them into Calc either by copying and pasting or saving as a csv and opening in Calc the columns containing numbers are changed to text and I can't use them in calculations. I have tried using different delimiters and changing the text file from Unix to Windows encoding (I don't remember this happening on my windows box at work) but the results are always the same.

In other words if I import a file containing the lines

Course,Credit Hours Course, Grade Received,Grade Point Value,Grade Points
Beg Comp (ENGL 1100),3,C,2
Med Term (MULT 1010),2,C,2

The 3 in the second column of the second row shows up visually as the number 3, but if I go to the cell formatting it shows "Text" and it can't be used in calculations. It is apparent at first glance because the default alignment for text is left justified in the cell and a number would be right justified.

  • What do you mean by "coming in as text"? Are they being turned into the text representation of the numbers (e.g. "three", "one-hundred-ninety-two", etc) or are they being imported as quoted strings, or are the cells being formatted as text? An example line from the CSV file you're having trouble with would be helpful, as would a screenshot of the problem you're having with it after import. – Lèse majesté Oct 13 '12 at 13:32

I had a similar problem with a text file which had numbers in currency format. ($nn,nnn.nn) (UK/US/CA/AU et al. style) Libre office decided it was text and put a hidden quote in front of the numbers. Grrrr.. I had to remove the "$"s and the "," commas from the csv file and reimport. Then it all worked like a bought one.

I might suggest changing your delimiter to something not a comma. I prefer tabs. You can't go wrong with tabs. Comma is problematical because it's a character used in numbers (but differently in different parts of the world). CSV was always a gigantic kludge. colon or semi colon might be OK.

  • Good thought, though I tried it at the time to no effect (and it totally shocked me), I also tried copying and pasting the text with the tab delimiters rather than importing a text file. Since then I have been though several upgrades of LibreOffice and Ubuntu so I'm not sure it is even happening any more. This one may never have an answer but yours is a good tip so I'll at least upvote it. – Dennis Nov 30 '12 at 12:26

I had the same problem with a csv file (with , separators). It seems that Libre office was interpreting numbers as text. For example: if I assign A1 to 0.1 and A2 to =A1+1, I get an error.

The fix was to switch local settings from French to English. (in: tools > parameters > linguistic settings > language > local settings)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.