Bug 145183 - FIND() function cannot detect a decimal point within a zero field
Summary: FIND() function cannot detect a decimal point within a zero field
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.4.7.2 release
Hardware: Other Linux (All)
: medium minor
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-10-17 10:53 UTC by Keith Sayers
Modified: 2021-10-18 13:41 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Example file maybe (9.05 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-10-17 18:03 UTC, Michael Warner
Details
FIND() function not finding (12.46 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-10-18 10:27 UTC, Keith Sayers
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Keith Sayers 2021-10-17 10:53:08 UTC
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.
Comment 1 m_a_riosv 2021-10-17 12:19:16 UTC
Please attach a sample file, and edit the title with a short message about the issue.
Comment 2 Julien Nabet 2021-10-17 12:30:09 UTC
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.
Comment 3 Michael Warner 2021-10-17 18:03:35 UTC
Created attachment 175796 [details]
Example file maybe
Comment 4 Michael Warner 2021-10-17 18:12:32 UTC
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.
Comment 5 m_a_riosv 2021-10-17 18:20:45 UTC
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")
Comment 6 m_a_riosv 2021-10-17 18:23:40 UTC
(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
Comment 7 Keith Sayers 2021-10-18 10:27:10 UTC
Created attachment 175809 [details]
FIND() function not finding
Comment 8 Keith Sayers 2021-10-18 10:28:06 UTC
     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.
Comment 9 Michael Warner 2021-10-18 13:23:39 UTC
I suggest you change your current formula to use the INT function instead of FIND, as suggested in comment 5.
Comment 10 m_a_riosv 2021-10-18 13:41:20 UTC
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.