I appreciate my LibreOffice from Ubuntu is out of date. Could anyone confirm if this bug is still present in latest version? I run in en_GB I open CSV with USD dollar values such as the following: hello,test,$1696 hello,test,$160 I select English (USA) and "detect special numbers" (otherwise it loads as text, and I cannot sort by the numbers) Calc shows the values with GBP eg £1696 Version: 24.2.7.2 (X86_64) / LibreOffice Community Build ID: 420(Build:2) CPU threads: 8; OS: Linux 6.8; UI render: default; VCL: gtk3 Locale: en-GB (en_GB.UTF-8); UI: en-GB Ubuntu package version: 4:24.2.7-0ubuntu0.24.04.2 Calc: threaded Jumbo It's easy to reproduce, let me know how you get along.
Please take a look to: https://ask.libreoffice.org/t/vlookup-formulas-fail-when-calc-opened-in-another-language/35436/2
Thank you for reporting the bug. Please attach a sample document, as this makes it easier for us to verify the bug. I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided. (Please note that the attachment will be public, remove any sensitive information before attaching it. See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F for help on how to do so.)
Thank you for the link. Yes, that seems to be the workaround. Calc unexpectedly changes the currency format from $ (USD) to £ (GBP) without any clear reason or logic. Attempts to manually set the correct formatting do not fully resolve the issue. Expected Behaviour: Calc should not change the currency format arbitrarily. Setting the language to English (USA) should retain the $ currency symbol. Users should not have to manually adjust formatting multiple times to display the correct currency. Calc should just "Detect special numbers" and retain the $. Raises a few issues/bugs 1. No reason or logic for Calc to change currency from $ to £ 2. Given (1) bug, I set to English (USA), and still bug (1) occurs. 3. Given (1) and (2) why I need to manually scroll and set that column to "US English", which then loads and removes the $ sign, so I am left with numbers and no $.
Created attachment 199175 [details] CSV showing the PR
Do you know that the $ is used in several countries?
This works as designed; but in this specific case, I'm inclined to agree that this is a bug. The reason of the problem is, that the settings chosen in the text import dialog are *completely not* about how the result should *look like* (be formatted in when imported), but how Calc should *read* the text values from the CSV. The process is: 1. User chooses locale (and other settings) in the dialog. Suppose "en-US"; 2. Calc reads a text like "abc", or "123", or "$1,234.56", and tries to interpret the text into a number *using the locale that user specified*. Given the locale chosen above (en-US), the program would keep "abc" as text, but would convert both "123" and "$1,234.56" into numbers 123.0 and 1234.56 (and will additionally remember, that the last value was imported using a currency number format - just *some* currency, not "US Dollar" - the same as when it imports a date, it converts it to the serial date number, and remembers, that it was *some* date format, not "long date format of Netherlands"); 3. After interpreting the values, it puts them to cells of a newly created Calc spreadsheet (having the locale configured for the whole program by the user; in this case, comment 0 tells us, that the program locale is en_GB). Putting a number with "currency" "tag" means, that the current cell's locale is used to choose the default currency format - the same, again, as we have for dates. But while with dates it's more than reasonable (a user from Europe, who imported a date written in en-US custom, would dislike to discover that they have to read it in an opposite direction, and what they perceived "5h of October" was in fact "10th of may", and they missed the deadline), with currencies, it's different. When a price is in USD, it's not the same as same amount of GBPs.