Created attachment 158973 [details] Example file from Excel Attached file contains a simple macro to concatenate a number in cell plus a “ .” (space dot) sequence. This call (with m1 being a loop variable) does not work in Calc: Cells(3, m1) = m1 & " ." However if some more characters are added to the concatenation, it works: Cells(4, m1) = m1 & " .cat" Using only a dot does not work in Excel either, and using only spaces works in Calc too. Steps to reproduce: 1. Open attached xlsm file 2. Run the macro in Excel and Calc Actual results: In the third row Excel concatenates the number and the space-dot sequence, Calc does not. In the fourth row the concatenation works as expected. Expected results: Calc concatenates a space and dot to the numbers in row 3. LibreOffice details: Version: 7.0.0.0.alpha0+ (x64) Build ID: bc898e2c2784e36ad4d4cdf6d962e39069d2c82d CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: Skia/Raster; VCL: win; Locale: hu-HU (hu_HU); UI-Language: en-US Calc: CL Also in: LibreOffice 3.5.0rc3 Build ID: 7e68ba2-a744ebf-1f241b7-c506db1-7d53735
Created attachment 158974 [details] Screenshot of the original document side by side in Excel and Calc after running the macro
* "1." is valid string that can be converted to number * assigning to cells evaluates the string "1.", and if the string is a number, then the cell value is set to that number. In this case, namely 1 * if the cell formats for the cells are set to Text instead of Number before the script is run, you will see the expected results when your code is run.
The resulting string is not "1." but "1 ." with a space between digit and dot, which is not a number. Also note the hu-HU locale that uses comma decimal separator (unless cells are formatted differently). However, the same (recognizing a number) happens in an en-GB locale with dot decimal separator, but only when running the macro, not with manual input. It seems as if VBA is trying to convert the string badly.
(In reply to himajin100000 from comment #2) > * "1." is valid string that can be converted to number > > * assigning to cells evaluates the string "1.", and if the string is a > number, then the cell value is set to that number. In this case, namely 1 > > * if the cell formats for the cells are set to Text instead of Number before > the script is run, you will see the expected results when your code is run. The question is, if digit + space + decimal separator is a number, just as digit plus decimal separator. By the way, in case of e.g. ru-RU locale, it's space plus comma that wouldn't work here. Entering the "1 .1" (assuming dot as decimal separator) in the Calc's formula bar, it's obvious that Calc ignores the space here. The question is: is this expected? Does Calc consider the space thousand separator maybe (and allows in in wrong place)?
(In reply to Mike Kaganski from comment #4) > The question is, if digit + space + decimal separator is a number No, it's not a number and shouldn't be. > Entering the "1 .1" (assuming dot as decimal separator) in the Calc's > formula bar, it's obvious that Calc ignores the space here. wtf.. I'd consider that a bug. > The question is: > is this expected? Does Calc consider the space thousand separator maybe (and > allows in in wrong place)? No, if the locale doesn't say the group separator is space then a space is not ignored as such (and a group separator followed by a decimal separator is invalid). But apparently the number parser tries to skip spaces at one too many place.
@Mike: does Excel accept any of these inputs as numbers? 1 .2 1. 2 1 . 2 . 2 . 2
Solution with all of those not numbers in https://gerrit.libreoffice.org/c/core/+/97895
(In reply to Eike Rathke from comment #6) > @Mike: does Excel accept any of these inputs as numbers? > > 1 .2 > 1. 2 > 1 . 2 > . 2 > . 2 No, none of them.
Great, thanks Mike.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/7186541219599e1b51ad35601c2cd015a329f360 Resolves: tdf#131562 decimal separator may not be surrounded by blanks It will be available in 7.1.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending https://gerrit.libreoffice.org/c/core/+/98074 for 7-0
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-0": https://git.libreoffice.org/core/commit/91ccd9fda9c953dbe88b99673491aabeb64ff330 Resolves: tdf#131562 decimal separator may not be surrounded by blanks It will be available in 7.0.0.2. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Verified in Version: 7.1.0.0.alpha0+ Build ID: 358674b87b8d9cd78079fb105aa81b50f4b5029b CPU threads: 4; OS: Linux 4.19; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded @Eike, thanks for fixing this issue!!
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/b8ff69726959ee4d148c12866d64601d86635bcc tdf131562: sc_macros_test: Add unittest It will be available in 7.1.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.