[libreoffice-users] Re: scalc interchange ods between english and spanish
>>> "Philip" == Philip Jacksonwrites: Hi Philip > Hi Uwe, > Regardless of the version of calc you are using, you can change the > decimal indicator. In the spreadsheet, select the cells concerned and > then Format > Cells > Numbers and select Language to Spanish (Spain) > and your numbers will go from 3.4 to 3,4 I tried that but I am not entirely sure it worked flawlessly. It seems however that the xlsx format (at least for simple spreadsheets) translate well the different formats. 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
[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"&"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
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"&"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 +, 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+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
Re: [libreoffice-users] scalc interchange ods between english and spanish
Hi Uwe, Regardless of the version of calc you are using, you can change the decimal indicator. In the spreadsheet, select the cells concerned and then Format > Cells > Numbers and select Language to Spanish (Spain) and your numbers will go from 3.4 to 3,4 So your colleagues won't know you ever had a problem. Philip On 27/06/17 12:55, Uwe Brauer wrote: > > > 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 > > -- Philip Jackson Domain le Theron 34210 Siran France Tel : +33.780508769 Open pgp public key : fingerprint = 92B9 5310 37BE C6FE 2B2C B922 26BD 500A 2354 3A63 author of "The Circle of Fifths" for improvising musicians Get it here https://www.books2read.com/u/bWnRM4 auteur : "Le Cycle des Quintes" pour musiciens improvisateurs disponibilité : https://www.books2read.com/u/bw8rv9 -- 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
[libreoffice-users] scalc interchange ods between english and spanish
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+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