We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

collect the content of non empty cells [closed]

asked 2018-02-08 10:52:41 +0200

moon gravatar image

updated 2018-02-08 11:11:18 +0200

Hi there,

I'm trying to collect alll the non empty cells from a range of cells and build a string out of it.

Assume a range C3:C23 with only some cells with data. example C4, C10, C15. I have no control on the number of non-empty cells. it could be 0 up to the total cells. I have also no control on the size of the range.

I need to build this kind of instruction : Concatenate("(",C4,",","C10,",","C15,")")

Is that possible at all ?

Thanks in advance.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-29 20:29:53.252984


It's rarely a good idea to create lists from entities in Calc. If you later want to analyze/modify the result you will experience lots of problems, and a SPLIT() function corresponding with the below mentioned TEXTJOIN() is still missing.

Lupp gravatar imageLupp ( 2018-02-08 12:26:04 +0200 )edit

Thanks for the advise.

moon gravatar imagemoon ( 2018-02-08 12:42:33 +0200 )edit

You may close the thread, of course, but you also may consider to mark the correct answer as accepted by clicking on the checkmark at the left. I judge the secvond way more helpful.
Expecting your conscious decision I will reopen the thread now.

Lupp gravatar imageLupp ( 2018-02-08 12:50:45 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-02-08 12:20:57 +0200

Lupp gravatar image

updated 2018-02-08 12:21:22 +0200

As of V5.2.2 the TEXTJOIN() function was implemented for the purpose. You may use =TEXTJOIN(";";1;C3:C23) e.g.

edit flag offensive delete link more


Thanks Lupp have to Upgrade then (still running

moon gravatar imagemoon ( 2018-02-08 12:29:42 +0200 )edit

answered 2018-02-08 12:41:25 +0200

moon gravatar image

It does the work Lupp. Thanks a lot ! I'll find a way to close the question.

edit flag offensive delete link more


By the way: Please prefer the tool 'add a comment' over posting an answer if your intended post not actually is an answer.

Lupp gravatar imageLupp ( 2018-02-08 12:53:43 +0200 )edit

Question Tools

1 follower


Asked: 2018-02-08 10:52:41 +0200

Seen: 254 times

Last updated: Feb 08 '18