Ask Your Question

How to copy multiple cell values to single cell, comma seperated

asked 2017-05-06 19:41:08 +0200

johnny538 gravatar image
         A          B
1|      Bob_    1, 4, 3, 2, 1
2|      Bob         1
3|      Bob         4
4|      Bob         3
5|      Bob         2
6|      Bob         1
7|     Alice_       1, 3
8|     Alice        1
9|     Alice        3

I need to auto-fill the value of for example B1. Which is B2:B6, seperated by a comma. Just like in the example above. The same for B7. But the real sheet is huge, and the rows underneath Name_ is variable.

Is there a formula that can make this easier for me ?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-05-06 21:13:49 +0200

Lupp gravatar image

updated 2017-05-06 21:48:25 +0200

=TEXTJOIN(", ";pCtrl;B2:B6) where pCtrl=0 will cause the creation of a position for each cell of the range even if it has empty text or is blank. pCtrl=1 ignores empty positions.

The function is implemented in V5.2.0 or higher. If the range is replaced by a subformula under array-evaluation there is the bug tdf#99625 which should be fixed as of V5.3.3.

A correspondig TEXTSPLIT function is not yet available.

Editing (also with respect to the comment by the OQ):

As any formula the one suggested above must be adapted to the specific situation, of course.
There is also this demo.
(General Advice: Don't mix up primary data and results evaluated based on these data in any column.)

edit flag offensive delete link more


B2:B6 would only work for Bob_ though, because Alice_ has fewer 'children', and thus the next 'parent''s 'children' would be included in Alice_'s range

EDIT: nevermind, I found a way to figure it out. Thanks

johnny538 gravatar imagejohnny538 ( 2017-05-06 21:18:49 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-05-06 19:41:08 +0200

Seen: 5,363 times

Last updated: May 06 '17