> 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+unsubscr...@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
