Concatenate Function inCalc

users
#1

All:

LibreOffice 6.2.2.2
CPU: 8 Threads
OS: Linux 4.2
Locale en_ZA

With previous versions of LibO, concatenate(b1:b200) would concatenate
all cells between b1 and b200.

With LibO 6.2.2.2, concatenate(b1:b200) returns the data in b1.

a) When was this functionality changed?
I didn't come across it in any of the 6.x betas/daily builds that I tested;

b) Why was this functionality changed?

jonathon

#2

I am running an older version of LibreOffice
Version: 5.1.6.2
Build ID: 1:5.1.6~rc2-0ubuntu1~xenial6
CPU Threads: 2; OS Version: Linux 4.4; UI Render: default;
Locale: en-AU (en_AU.UTF-8); Calc: group
running on Linux Mint 18.3

the function concatenate(text1:text4) gives the result text1
the function concatenate(text1, text2, text3, text4) gives the correct
result of text1text2text3text4

regards

Mike

#3

I am running 6.07 and =CONCATENATE(A2:A4) gives #VALUE
=CONCATENATE(A2,A3,A4) concatenates A2, A3 and A4 correctly.
Steve

#4

Hi *,

use CONCAT instead of CONCATENATE. CONCAT will accept cell-ranges.

Regards

Robert

#5

CONCATENATE must either fail to accept ranges (with an error), or accept
them and provide the combination that acceptance suggests. It is a bug that
it accepts ranges, but returns something else.

#6

Hi Michael,

CONCATENATE must either fail to accept ranges (with an error), or accept
them and provide the combination that acceptance suggests. It is a bug that
it accepts ranges, but returns something else.

Have opened Calc and looking for fuction CONCATENATE. Help in the dialog
says: "Combines several text items into one."
Then looked for CONCAT. Help in the dialog says: "Combines several text
items into one, accepts cell ranges as arguments."

I don't see in the help CONCATENATE accepts cell ranges.

Regards

Robert

#7

Why are we defending invalid output of a function?

When I send invalid data like range to a function, say ABS(c1:c4), I expect
it to return an error (#VALUE! comes up in linux).

In linux on LO 6.0.7, I'm getting the 2nd value in the range as a valid
output of the function CONCATENATE(C1:C4). That is erroneous. broken.
should-not-happen.

This thread is about CONCATENATE being disfunctional in that it accepts
ranges, but does not act on them correctly. Please stop referring to CONCAT
as the reason that CONCATENATE is broken.

However, in regard to CONCAT, I see zero reason for the CONCATENATE
function to be separate from CONCAT. CONCATENATE should be replaced with
an alias or redirect to CONCAT. But thats not a bug, just poor design.

#8

Howdy,

I believe that the difference is in where the function hails from:
CONCATENATE is defined in the ODF standard.
CONCAT comes by way of Excel support.

I think you have a point about how the system should respond when
erroneously using ranges for the ODF function (not supported) and could be
worth opening an issue.

Best wishes,

Drew

#9

Hi Michael,

This thread is about CONCATENATE being disfunctional in that it accepts
ranges, but does not act on them correctly. Please stop referring to CONCAT
as the reason that CONCATENATE is broken.

Please red the first mail of jonathon.
"With previous versions of LibO, concatenate(b1:b200) would concatenate
all cells between b1 and b200."
He has been looking for a functinality of CONCAT and wanted to use
CONCATENATE, which neverd worked in this way.

I try to help to solve this problem.

And by the way: the function CONCATENATE gives "#VALUE!" on my system
with OpenSUSE 15, 64bit rpm Linux.

Regards

Robert

#10

In 6.2.0.3 on Windows, the function CONCATENATE(a1:a3) returns the value of a1

This should be reported a a bug...

#11

Hi Luuk,

In 6.2.0.3 on Windows, the function CONCATENATE(a1:a3) returns the value
of a1

This should be reported a a bug...

So please report this, if you could see the buggy behaviour in LO
6.2.2.2 also. I can't report it, because I couldn't reproduce it with LO
6.2.2.2.

Regards

Robert

#12

It doesn't matter if concat or concatentate is the correct command to
use. Neither concat(a20:a200) nor concatentate(a20:a200) return the
expected the result. Both return the value in a20.

Prior to LibO 6.2.2, (a20:a200) would return a single string, containing
the contents all the cells between a20 & a200, inclusive.

#13

Hi Michael,

CONCATENATE must either fail to accept ranges (with an error), or accept
them and provide the combination that acceptance suggests. It is a bug that
it accepts ranges, but returns something else.

Because CONCATENATE expects a list of simple references, it performs an "implicit intersection", if instead of a simple reference a range is given.
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#ImpliedIntersection

That is the reason, that sometimes you get the content of a single cell as result and sometimes an error.

Kind regards
Regina

#14

That implies that it was a bug that when it saw a range, it returned the
expected result.

jonathon

#15

Hi Michael,

> CONCATENATE must either fail to accept ranges (with an error), or
> accept them and provide the combination that acceptance suggests.
> It is a bug that it accepts ranges, but returns something else.
>

Because CONCATENATE expects a list of simple references, it performs
an "implicit intersection", if instead of a simple reference a range
is given.
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#ImpliedIntersection

That is the reason, that sometimes you get the content of a single
cell as result and sometimes an error.

That's a crazy idea that will lead to all kinds of obscure errors. Far
better to unconditionally return an error, IMHO.

#16

Hi Jonathon,

It doesn't matter if concat or concatentate is the correct command to
use. Neither concat(a20:a200) nor concatentate(a20:a200) return the
expected the result. Both return the value in a20.

I couldn't confirm this with
Version: 6.2.2.2
Build ID: 2b840030fec2aae0fd2658d8d4f9548af4e3518d
CPU threads: 6; OS: Linux 4.12; UI render: default; VCL: gtk3;
Locale: en-US (de_DE.UTF-8); UI-Language: en-US
Calc: threaded
(OpenSUSE 15, rpm Linux)

I write down some text in A1, B1 and C1 and then in D1 =CONCAT(A1:C1) .
Shows the content of all fields together in D1.
Then the same with =CONCATENATE(A1:C1) .
Shows #VALUE!

You have tested with LO 6.2.2.2? Please show the Build ID here. Is it a
build of the Linux-distribution or is it a build directly from LO?

Regards

Robert

#17

I decided not to report this as a bug.

Then only 'bug' i could find is that CONCAT is missing from the help,

where CONCATENATE leads to:
https://help.libreoffice.org/6.2/en-US/text/scalc/01/04060110.html?System=WIN&DbPAR=CALC&HID=SC_HID_FUNC_VERKETTEN#bm_id3153084

I (anyone) want to create a bug, you can do it here:
https://bugs.documentfoundation.org/enter_bug.cgi

#18

Hi Luuk,

I decided not to report this as a bug.

Then only 'bug' i could find is that CONCAT is missing from the help,

where CONCATENATE leads to:
https://help.libreoffice.org/6.2/en-US/text/scalc/01/04060110.html?System=WIN&DbPAR=CALC&HID=SC_HID_FUNC_VERKETTEN#bm_id3153084

I (anyone) want to create a bug, you can do it here:
https://bugs.documentfoundation.org/enter_bug.cgi

Already done: https://bugs.documentfoundation.org/show_bug.cgi?id=124502

Regards

Robert