I have seen for long that when any xls worksheet is opened,the numbers,dates,( even text starting with a number in a cell) are imported with an 'apostrophe' behind them.So,the cell content is not treated as number any more.This gives rise to a nightmarish prospect when working with a xls file in OO / LO. To be able to do any mathematical operation on the content is impossible without first removing the apostrophe in the cell individually. Is it because MSO has any invisible apostrophes in the cells starting with a numeral ? I cannot say. But if so, OO / LO should remove such an apostrophe. OR is this apostrophe added by OO / LO ? I do not see any reason for doing that and crippling usefulness of it's import. Eitherway, it is a bug in LO / OO and this is a major bug. This severely limits the very usefulness of the import facility. I have seen descriptions of tricks to removing the apostrophe, but they are obviously hacks. Why should LO not be able to treat dates and numbers correctly when importing ? I have seen numerous discussions on the Net on this topic. But I have not seen this referenced as a Bug. If this is a duplicate bug, kindly mark so, but remove this bug,please on priority.
The apostrophe is used to mark numbers and dates as a text, see https://help.libreoffice.org/Calc/Formatting_Numbers_as_Text . But they should be in front of the numbers and dates I think that it is not a general problem. I am not able to reproduce it here. Could you please attach a sample document that is showing the problem?
We are not able to move forward if we do not understand the problem. Please, attach a test file so we could continue. The problem is not easy to understand and reporoduce => can't block the release => lowering the severity ab it.
Created attachment 82818 [details] numbers,dates preceded by apostrophe
(In reply to comment #2) > We are not able to move forward if we do not understand the problem. Please, > attach a test file so we could continue. > > The problem is not easy to understand and reporoduce => can't block the > release => lowering the severity ab it. Thank you for the promptness with which you responded to the reported issue. By any standards it is great, by OO standards - where a reported issue did not elicit response for years and which had me totally put-off - this promptness is unthinkable. Coming to the issue, my office, personal systems are all on Linux / LO. My only dual boot Windows installation did not work. So, this had to wait. You are right. I created an xls file in Excel and opened it in LO. One column containing numbers explicitly formatted as numbers and another column with numbers, but with default formatting. Both had no apostrophe in LO. So, this is not a general problem. But I have attached an excel sheet which I downloaded from my bank. This file when opened in LO treats all numbers and dates as text. I cannot say whether the apostrophe before the numbers / dates was introduced by LO while first reading it or was in the original excel file produced by the Bank.But this file, when opened in MSO, does not show the apostrophe in cell editing or formula bar and the data are also treated as DATE / NUMBER as relevant. Only clue to them being text is the data are all left aligned. The ditto behaviour in calligra sheets as well. I have also observed any excel file produced by Java Jasper Library behaves exactly same way in LO. Though the datta in jasper report is treated as number, when exported to execl, all dates and numbers have apostrophe before them.I do not know if the bank also used Jasper to produce Excel.
Created attachment 82906 [details] Screenshot from MS Office 2013 showing the warning about the formating This is what I see in MS Office 2013. Please notice: + all values are on the left side + I do not see any apostrophe in any cell + most values have a warning (green triangle in the top left corner of the cell) + the cell F6 has the warning that the number is formatted as a test or preceded by an apostrophe + there is no apostrophe in the edit filed
Created attachment 82907 [details] Screenshot from MS Office 2013 showing the formating of the cell F7 This is just another view from MS Office 2013. Everything is the same as in the previous screenshot. Please notice: + the cell is formatted as "General Number" + I do not see any apostrophe anywhere
Created attachment 82908 [details] Screenshot from LO 4.0.4.2 with en_US locale Here is what I see in LO 4.0.4.2 in English locale. Please notice: + + all values are on the left side like in MS Office + I do not see any apostrophe in any cell like in MS Office + I do not see any visual warning in the cells but LO does not have this feature + the cell F6 is formatted as "Genelar Number" same as in MS Office + thousands are normally delimited by comma ',' and decimal by dot '.' in English locale + there is the apostrophe visible in the edit field So, the number is valid English number. But the apostrophe causes that is is displayed as text on the left side of the cell.
Created attachment 82909 [details] Screenshot from LO 4.0.4.2 with cs_CZ locale Here is what I see in LO 4.0.4.2 in Czech locale. Most of the things are as in the English locale but please notice: + the format is still "General Number" but + thousands are delimited by space ' ' and decimal by comma ',' in Czech locale + there is no apostrophe in the edit field I think that the apostrophe is not displayed in the edit filed because it is not valid number in the Czech locale. The thousand and decimal separator is not valid in Czech, so it has to be a text.
After all I think that everything works as expected. The sheet looks the same in MS Office and LO. The values are formatted as text in both offices. The main difference is that LO does not have a visual waring (green rectangle) about the enforced text formatting. LO only shows the apostrophe in the edit field instead. I think that you bank want's to avoid formatting problems. It want's to show the same content in all locales and therefore enforces the text format. I personally thing that it is a bit ugly because such values could not be used in some computing easily. Do you see the same please? If not, could you please attach your screenshots?
(In reply to comment #9) > After all I think that everything works as expected. The sheet looks the > same in MS Office and LO. you are missing the important point. You may check it when you have opened the file in MSO. It lets you work with numbers as numbers and dates as dates.It senses the true nature of data. It will keep them left aligned, but will not come in the way of doing any mathematical operation on them. In LO, you cannot do any mathematical operation on them. If you highlight multiple cells, sum of the cells is zero.The numbers with apostrophe behind are shown as numbers in cell format dialog. You change them to specific number format, no error is generated.But there is no change in the status either. You will not be able to convert them. so, to deal with them you either have to manually and tediously remove apostrophes, or resort to ugly hacks like " find '^.' and replace with '&' ". Finding ' and replacing with nothing did not work for me, though some people have reported it to work. LO fails to find apostrophe. Anyway,they are not for common users. As far as common users are concerned, the file from the Bank is not workable. Removing apostrophes manually is way too tedious for large data.Changing the format will not work. Interestingly, calligra sheets will open the file perfectly and let you work with numbers as numbers and date as dates though they are left aligned as they are formatted as text. additionally, when I see the cell format property, it will correctly show the format as text, not as general number.Even then,without changing the format, it will let you work with numbers as numbers and date as dates. If you change the format to number, it will save the cell as number when saved even in OD format and remember it as number when opened again. But this ods file if opened in LO will still be showing the numbers as text and not deal with them as numbers. You open the same ods file again in calligra sheet, the reformatted cells would remain re-formatted. It is not a question of formatting. It is a question of dealing with data. Formatting a numerical data as text with apostrophe is end of story in LO for that number. You cannot have it as number again through any means other than physically removing apostrophe or hacking at it.
Created attachment 82987 [details] Screenshot from MS Office 2013 showing sum of the text formated cells This is what I see in Ms Office 2013. The sum of the range of cells F5:F7 is zero => MS Office 2013 handle the cell as text => LibreOffice are compatible here.
Parameshwara, please, what version of MS Office you used for testing? It seems that MS Office 2013 ignores the numbers that are formatted as text. It works the same as LibreOffice. Otherwise, I agree that LibreOffice does not allow to switch between the formats easily. I have changed the title to describe this existing problem.
I have just tested it with Calligra Sheet, version 2.5.4. The behavior is the same as in LibreOffice and MS Office 2013, the sum of the range of cells F5:F7 is zero. I wonder how you managed to get sum of numbers. Have you reformated the cells before? Could you please attach a test document where the sum works in MSO and Calligra?
1. The "numbers" are not numeric cell content but text intead, this is important for all following behavior. Usually you calculate with numeric content in a spreadhseet, and not with text. Calculating with text is a bad idea, as follows. 2. Because of the textual content, if the file is loaded in Calc in a locale where the separators match those of the text (i.e. '.' dot decimal separator and ',' group separator in this case) the ' apostrophe is prepended to the input line (it is not part of the cell content so can not be searched for) such that editing the textual string will not change the cell content to numeric. 3. Excel has some weird behavior that when calculating with binary operators (e.g. =A1+A2) where the content is text but would be interpretable as numbers according to the current locale it does interpret it as numbers. Using functions like SUM() that take a range argument the text is not interpreted as numbers but ignored instead. The latter is standard spreadsheet behavior and Calc does the same. 4. Load a file with such (A1+A2) calculations in Excel after having switched your environment to a locale that does not match the separators, e.g. one where the decimal separator is ',' comma, and you will see the mess that results, at best the "numbers" will not be interpreted anymore and #VALUE! results, at worst the numbers can be differently interpreted, for example "12,345" with ',' group separator vs ',' decimal separator.. This is why calculating with interpreted text is bad. Calc so far mimicks the Excel behavior. Now for solutions to the problem if you encounter such a file: As you already noticed you can select the cells you want to change and using Find&Replace with regular expressions enabled you can search for ^. and replace with $0 and hit ReplaceAll. Another and maybe more comfortable solution is to use the ConverTextToNumbers (CT2N) extension, you may find it at http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates Last but not least, you mentioned that your bank produced the file, I'd ask them to produce proper spreadsheet files with numeric content and not just dump strings into text.
Created attachment 82993 [details] In MSOffice, addition on cells can be performed Perhaps you are using the sun(cellx:celly) function. But I did a simple cellx + celly addition which worked.
Created attachment 82994 [details] In MSOffice, the warning menu offers to convert In MSOffice, the warning menu offers to convert the number formatted as text to number. And that works very well. That is exactly What LO needs to have.
Created attachment 82995 [details] Calligra Version 2.5.4 shows sum of cells and performs addition
Please see the attachments. In MSO, addition on cells could be performed . Perhaps you are using the sun(cellx:celly) function which as you correctly said, does not work. But I did a simple cellx + celly addition which worked. In MSOffice, the warning menu offers to convert the number formatted as text to number. And that works very well. That is exactly What LO needs to have. If LO can easily convert from text-formatted number to number, what else it does or it does not do will not be very important. In Calligra sheets, version 2.5.4, sum(cellx:celly) does not work as you pointed out. But simple addition can be performed on the cells. Also,when multiple cells are highlighted, on the bottom-LH, the sum of the cell contents is correctly shown. Internally this display may be tied to simple addition, rather than to sum(x:y) function. Why, can't we have LO smarter than Calligra and MSO ? PS : the status of bug seems to have been messed up by me.I have re-opened.
@Eike Rathke, I am not a programmer, but LO user. But I do understand the technology involved, to that extent I am not a common user. In principle, what I believe is LO or any open-source program should aim to be better and smarter than proprietary counterparts and they need not be our references. But to the extent these programs define the baseline, we need to refer to them. If LO already has a convertToNumber Extension, will it not be possible to enhance it and merge it with the Mainstream LO so that like in MSO, we can have a warning when numbers and dates formatted as text are encountered ? Also, an option in the warning menu to convert cell-content to numbers ? May be this bug report can be changed to Enhancement Request ? your suggestion to tell Bank to give a proper portable spreadsheet may not work as long as MSO keeps handling this easy. Also, bank is a big corporate, it's staff are also users of the program,they do not understand and do not appreciate these finer points. Look, even jasper library,when exporting to Excel format, exports numbers formatted as text.
I think that it is fair to keep this as enhancement request. It would be nice to make this more comfortable for users. Maybe, we could create an easy hack from this. The solution might be to integrate the "Convert Text to Number" extension and make it accessible from the context menu (right click on cell or selection) The extension can be found at http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates. Well, this bug is already very long. It took quite some time to come to this point. It might be useful to open a new enhancement bug. Parameshwara, would you mind to do so, please?
(In reply to comment #20) > > Well, this bug is already very long. It took quite some time to come to this > point. It might be useful to open a new enhancement bug. Parameshwara, would > you mind to do so, please? Thank you both Petr and Eike.You have helped to take the bug to a fair conclusion. I have opened a new bug report exactly on this suggestion, which you can find here https://bugs.freedesktop.org/show_bug.cgi?id=67342