[libreoffice-users] Re: scalc interchange ods between english and spanish

   > Hello,
   > I exchange ODS and XLS files between the French Canadian (decimal
   > separator « , ») and English US/Canada locales (decimal separator « .
   > ») without issues. When the number is already in a cell on a worksheet,
   > the application will use the locale to display a decimal number in the
   > correct way.

   > However, you cannot transfer files in text format (such as CSV)
   > directly from one to the other because the formatting of the decimal
   > numbers will conform to the current locale when the file is created: if
   > I import a CSV file created from the "English" version, afer the import
   > I must do a global replace of « . » to « , » to make numbers become
   > numbers again. Also, when going the other way, the CSV separator is
   > usually a « ; » to allow decimal numbers to contain a comma as decimal
   > separator, which is something Excel does not always handle very well.


Ok maybe I mixed CSV with excel files and that is where my confusion
starts. You are right the xlsx format seems pretty save.


   > So far, the best way I found is to save as XLS (or XLSX) from LO (Excel
   > considers ODS files as "broken" and does not preserve formulas), do the
   > changes in Excel, then open the XLS file in LO and save in ODS. Just
   > remember here that some page and cell formatting may be lost in the
   > exchange, so do not attempt anything fancy on that front :-).

Still the excel files, when opened in excel contain cells with small
green edges, which according to the documentation is a indication of
possible format violation.


   > If you use formulas that format numbers (=TEXT()), you will need to
   > have some intelligence to use the proper separator when encoding the
   > number format (more so for Excel than for LO). What I usually do is
   > have a cell that I name SEP that contains a formula like this:

   > =IF(ISNUM("3.4");".";",")

   > Then when I need to format a value with the =TEXT() function, I use it
   > this way to have a number with one decimal digit:

   > =TEXT(value;"0"&SEP&"0") or, if you prefer,
   > =TEXT(value;CONCATENATE("0";SEP;"0"))

   > In English locales, this translates to: =TEXT(value;"0.0"); in the non-
   > English locale, it becomes =TEXT(value;"0,0")

   > I hope this helps.

Thanks it does, will try to avoid to use the TEXT function, though.

Uwe Brauer 


-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted