I have encountered what seems to be a structural fault in CALC Version: 6.4.7.2 Build ID: 1:6.4.7-0 ubuntu0.20.04.1 - the FIND() function cannot detect a decimal point within a zero field. Suffixed is an extraction from a database - row 17, columns W to Z. The command in cell Z17 is =LEFT(W17,FIND(".",W17)-1) and the result is in cell Z17 Column : W X Y Z Row 17 : 0.00 18 53 #VALUE! In other cells in that column the command works correctly and if I change that decimal point to a comma, then FIND() does find it. I have tried the same thing in Gnumeric with the same result.
Please attach a sample file, and edit the title with a short message about the issue.
Also give a try to a newer LO version, 6.4.7 is quite old. You can use https://launchpad.net/~libreoffice/+archive/ubuntu/ppa Anyway, since dealing with decimal separator is a bit specific, you may reproduce this too on recent LO version.
Created attachment 175796 [details] Example file maybe
In the example file I attached, column A is formatted to display 2 decimal places. If you enter in 0 (or any other whole number for that matter) it shows just the whole number in the formula bar (it shows just "0"), and the number with two decimal places in the sheet ("0.00"). If you enter in a number with an actual fractional component, it shows that in both places. For example, on row two I entered 0.01 and that displays both in the formula bar and the sheet. There, the formula in column D works as expected. So, it seems clear that for any whole number, it's not considered to have a decimal point, even though one is shown on the sheet. I can see why this would surprise a person, but I don't know if it should really be considered a bug.
Don't mix the value inside the cell with the format of how it is showed. And it can be point or command depending on the locale. If you want to get the integer part of the value, you can use =INT(A1) =ROUND(A1) as text =TEXT(A1;"0")
(In reply to m.a.riosv from comment #5) > Don't mix the value inside the cell with the format of how it is showed. And > it can be point or command depending on the locale. > > If you want to get the integer part of the value, you can use > =INT(A1) > =ROUND(A1) > as text > =TEXT(A1;"0") point or comma
Created attachment 175809 [details] FIND() function not finding
My appreciation for so many responses so quickly. The main thing sought seems to be a selection from my spreadsheet. Attached. Column A is now hard coded to be the source for the subsequent formulae. FWIW the original is a monthly spreadsheet I use to check phone calls. At some point I was advised to try the same thing in Gnumeric. I did and got the same result. I realise that as a one-off solution I could hard code those #VALUE! cells or for longer use use a formula built on MOD() or expand the current formula to something like IF(VALUE(A1)=0,0,FIND .......). If this does not qualify as a bug that would be fine by me. Comment # 1 on bug 145183 from m.a.riosv Please attach a sample file, and edit the title with a short message about the issue. Comment # 2 on bug 145183 from Julien Nabet Also give a try to a newer LO version, 6.4.7 is quite old. You can use https://launchpad.net/~libreoffice/+archive/ubuntu/ppa Anyway, since dealing with decimal separator is a bit specific, you may reproduce this too on recent LO version. If I may, I would rather not do a partial upgrade. I use Kubuntu, currently version 20.04, in due course I would expect to get v22.04 which should bring me a complete upgrade.
I suggest you change your current formula to use the INT function instead of FIND, as suggested in comment 5.
Again, a number without decimals, don't have a decimal point. If you insist in your way you need to convert the number in text with A1 format. =LEFT(A7;FIND(".";TEXT(A7;"0.0");1)-1) but then you should search for the decimal separator in your locale, point or comma. In any case there is no bug here.