Ask Your Question

concatenation function capable of accumulating ranges at once (like Summary function)

asked 2017-09-23 12:15:46 +0200

dimzev gravatar image

updated 2017-09-23 17:20:52 +0200

I want merge the text from many cell on one row in one cell. How can do this with out typing W1=A1&B1&C1... Can select with drag mouse and give one order ?

edit retag flag offensive close merge delete


The subject of your question may be misleading. You are not asking for a way of merging cells, but for a concatenation function capable of accumulating ranges.

Lupp gravatar imageLupp ( 2017-09-23 13:43:03 +0200 )edit

I dont speak english language very well and i cant use the right word every time .

dimzev gravatar imagedimzev ( 2017-09-23 17:13:08 +0200 )edit

No problem insofar. My English may be poorer than yours. It's just that "merge" cells is used in a very different meaning in the spreadsheet context while the term "merge" is rarely applied to texts..

Lupp gravatar imageLupp ( 2017-09-23 18:38:37 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-09-23 13:54:06 +0200

Lupp gravatar image

updated 2017-09-23 14:28:25 +0200

Among the standard functions of Calc specified by 'OpenFormula' there are none capable of concatenating ranges in an accumulating way. Starting with V 5.2 two new functions for the task were implemented for compatibiltiy with an obscure version of Excel. After some bug fixing they are actually usable now (V 5.4.y.z e.g.). However there is not yet a satisfying help text. See bug tdf#99517.

If you want to simply concatenate, your example should look like =CONCAT(A1:C1). If you want to insert a delimiter between any two cell contents you can use =TEXTJOIN(myDelimiter; myMode; A1:C1). 'myDelimiter' is the string to insert, myMode can be 0 or 1 distinguishing whether (0) the delimiter will also be inserted if an adjacent text is empty or (1) empty cells are ignored.

See also this attached demo.
(Original filename: ask132310AccumulatingConcatenation_1.ods)

edit flag offensive delete link more


I try to use =CONCAT(A1:C1) but the result is : #NAME? I see your attached demo and in IF column i see only same result #NAME? i dont know the way to go to (Original filename: ask132310AccumulatingConcatenation_1.ods) same result and with =TEXTJOIN(myDelimiter; myMode; A1:C1) i dont understand what make wrong...

dimzev gravatar imagedimzev ( 2017-09-23 17:20:47 +0200 )edit

I can only suggest to check for the version. As I already mentioned the new functions CONCAT and TEXTJOIN are only available in a debuged state in recent LibO. Use a version >= 5.3.3, please.
I also cannot test if your locale (Greek?) can handle everything in the appropriate way. You may try to enable the option 'Tools' > 'Options' > 'LibreOffice Calc' > 'Formulae' > 'Formula Options' > 'Use English function names'.
Everything surely worked correctly on my system: Win10, LibO64 V 5.4.1

Lupp gravatar imageLupp ( 2017-09-23 18:28:57 +0200 )edit

By the way: I am a German residing in Germany. Nonetheless I am using LibO always with UI-language 'Englsh (UK)' and mostly also with the respective locale.
Of course, for many computers force-localised by MS and connected to a localised keybord, this may not be a realistic option.

Lupp gravatar imageLupp ( 2017-09-23 18:34:39 +0200 )edit

i use " Version: Build ID: 1:5.1.6~rc2-0ubuntu1~xenial2 CPU Threads: 4; OS Version: Linux 4.10; UI Render: default; Locale: en-US (en_US.UTF-8); Calc: group

i dont use greek menu only Eng-US and as you see only Linux (ubuntu or others distros) , i am and i live in Greece ( i dont try upgrade now my Librooffice version because i am afraid some mistake and i dont want add more problems :)

dimzev gravatar imagedimzev ( 2017-09-23 22:34:13 +0200 )edit

and something else if you can help me: W1=A1&B1&C1---- if want keep the formating (color) from text in C1 cell how i can do this?

dimzev gravatar imagedimzev ( 2017-09-23 22:36:14 +0200 )edit

We have very different questions now:
-1- Is there replacement for CONCAT, TEXTJOIN for versions <5.3.3? Yes. I can supply user code in BASIC.
-2- How can I assign some (one) of a cell's format properties to another cell by a formula? If I understood your question correctly: There are no standard functions to achieve this. You will need user code. Much better: Redesign the sheet keeping the data or formula results ruling the concerned properties explicitly in dedicated cells (a column).

Lupp gravatar imageLupp ( 2017-09-24 01:06:25 +0200 )edit

-2- (continued) If you accept the advice you can use the contents/results in the new column manifold. Conditional formats and the STYLE function can be used to visualise related conditions.
To get more details concerning -2- ask a new independent question, please. (One thread, one question.)

Lupp gravatar imageLupp ( 2017-09-24 01:10:18 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-09-23 12:15:46 +0200

Seen: 2,889 times

Last updated: Sep 23 '17