Hello Using en_GB Calc I need to manually remove commas when pasting numbers from bank statements. Could Calc handle this automatically? eg = 1,947.68 - 1000 Err:509 Calc knows we use the decimal . to separate decimals and comma to indicate thousands. Can Calc understand what is typed? It could simply remove the comma when it accepts the number. If I paste 1,947.68 directly into a cell, Calc does interpret and remove the comma.
= 1947.68 - 1000 works for me, I think there is an invisible character, that makes it fails. To view it, paste it in writer and set up Menu/View formatting marks
(In reply to m.a.riosv from comment #1) > = 1947.68 - 1000 > works for me, I think there is an invisible character, that makes it fails. > > To view it, paste it in writer and set up Menu/View formatting marks Hello Thank you for checking this. Could you try wthe comma? 1. Click in a new cell 2. type, or paste this: = 1,947.68 - 1000 3. See Err:509
Sorry I copied without the comma, but it works with the comma. Have you tried to view if there is an invisible character?
Created attachment 168283 [details] entering numbers with commas
(In reply to m.a.riosv from comment #3) > Sorry I copied without the comma, but it works with the comma. > > Have you tried to view if there is an invisible character? Many thanks for your reply. I did wonder this, so I removed all the spaces. =1,947.68-1000 Same issue. What version of Calc are you using? I am on an old release. I will attach a screenshot.
Repro in Version: 6.4.6.2, Build ID: 1:6.4.6-0ubuntu0.20.04.1 CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3; Locale: en-US (en_US.UTF-8); UI-Language: en-US Calc: threaded No repro in Version: 7.0.4.2 Build ID: dcf040e67528d9187c66b2379df5ea4407429775 CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: kf5 Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded No repro in Version: 6.4.7.2 Build ID: 639b8ac485750d5696d7590a72ef1b496725cfb5 CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: kf5; Locale: en-US (en_US.UTF-8); UI-Language: en-US Calc: threaded
From the screenshot taking the assumption you are on Ubuntu as well. From my test the repro is on Ubuntu - hence I'd assume a bug in Ubuntu packages/installation of LibreOffice. Now taking into account that initial installs of LibreOffice (using distro packages) on Ubuntu 20.04 are known to have many problems, which seem to be completely unrelated to each other, I'd suggest to turn to ask.libreoffice.org and have a look at one of the following question (and their respective solution - which btw is always the same): [1] https://ask.libreoffice.org/en/question/281050/menu-bar-is-just-boxes-there-are-no-words [2] https://ask.libreoffice.org/en/question/271623/cups-authentication-not-working-correctly/ [3] https://ask.libreoffice.org/en/question/268614/broken-libreoffice-installation-on-ubuntu-2004/ [4] https://ask.libreoffice.org/en/question/249667/lubuntu-2004-libreoffice-6432-my-samsung-ml-1610-printer-does-not-print-text-correctly-through-libreoffice-it-prints-graphics-correctly/
Many thanks for your investigation. Looks like it's maybe already fixed in newer versions. Sorry to see so many issues with Ubuntu packages.
Seems to get more complicated: Repro in (Mint 20, Ubuntu based OS, but using TDF packages) Version: 7.0.4.2 Build ID: dcf040e67528d9187c66b2379df5ea4407429775 CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF8); UI: en-US Calc: threaded
Changing Function-separator from "," to ";" in Tools -> Options -> LibreOffice -> Formula -> Section: Separators -> Option: Function" prevents the ERR:509 to occur. Seems to be some conflict (full en_US language environment)
I also wondered. In countries that don't use the full-stop as decimal place marker - does this work for them? Eg Poland. As I understand, a Polish user would expect to write in their locale 1.000,00 - 10 In Japan they use a comma separator at the 4 digit mark. eg 1,0000.01 Would that be supported? There's another test case with £ GBP pound symbol 1. =£1000 2. Err:501 Similar example with $ doesn't work, as that's a special character, there's probably no way to use that in the formal box? 1. =$1000 2. #REF!
ere's another test case with £ GBP pound symbol > > 1. =£1000 > 2. Err:501 > > > > Similar example with $ doesn't work, as that's a special character, there's > probably no way to use that in the formal box? > > 1. =$1000 > 2. #REF! Please don't mix issues, though these examples are no issues at all and are to be expected since =£1000 is not a recognized number but text and =$1000 no a valid reference.
Steps to reproduce (from testing) - Open a new Calc sheet - Assure in Tools -> Options -> LibreOffice -> Formula -> Section: Separators -> Option: Function separator is set to "," (comma), which e.g is default for English (USA) - Insert: =1,947.68-1000 Actual Result: ERR:509 Expected Result: 947.68
Now I'm not sure whether this is an enhancement request or it is to be considered a bug based on current supposed/intended functionality.
This mix within formulas where the group separator equals the parameter separator (or any other operator) won't be supported.
Good evening, It is a real shame this bug was closed. It does feel like a confirmed issue as it works as normal in Google Sheets, and is easy to reproduce. Google Sheets doesn't suffer this issue. Sheets also retains the formatting entered correctly. Can you confirm if you can reproduce the same? Jul-19-2019 10 Aug 2019 =A1-A2 -22 Days different We get CSV files from all over the world, USA, Japan, UK, France In the case of the USA, they often (but not always) put the month before the day, and then end as /19 or /2019 or with hyphen. There's no reason for us to need to force anything if it can be auto detected It's a bit strange for us to need to import into Google Sheets, and then export before loading into Calc. Probably this column forcing on CSV load was only introduced in 7.x which hasn't made it into Ubuntu stable LTS yet. Sorry I am not that up to date.
(In reply to Jonny Grant from comment #16) > Jul-19-2019 > 10 Aug 2019 Comment on wrong bug, you are confusing this with bug 127893.