Re: [libreoffice-users] 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.

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 :-).

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.

Rémy Gauthier.


Le mardi 27 juin 2017 à 10:55 +0000, Uwe Brauer a écrit :
> Hi
>
> > I am using the english version of LO 5.3 and therefore I use the «.»
to
> indicate decimals: 3.4
>
> However I have to interchange the documents with my colleagues which
> > using the Spanish version (of excel) so I must send xlsx or xls
format
> anyhow.
>
> How I can I ensure that the spanish excel version of my colleagues
> > interpret 3.4 as a number, since in Spanish the convention is either
3,4
> or 3'4?. I hoped that could be internally solved without any
> specification but it seems not to be the case.
>
> Any suggestion would be welcome. Thanks
>
> 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