I would like to join cells with text if it starts with "substitute".

Now I know joining the text without a condition creates this formula:

=JOIN(" ";F9:AE9)

However on the y-axis on this row are 3 different kinds of cells; y, n, substitute (time). It means yes, no or substitute with a specific time like; substitute (16.30-17.30). I would like the result that everything is in one cell like; Substitute (16.30-17.30) Substitute (17.00-18.00) etc. So without the n's and y's.

I thought it would be:

=IF(F9:AE9="^substitute";JOIN(" ";F9:AE9))


=JOIN(" ";F9:AE9;IF(F9:AE9="^Substitute"))

However this does not work.


Say we have data in A1 through A10 and we want to JOIN() only those values beginning with the text substitute.

We can first filter the data and join the results of the filter:

=join(" ",filter(A1:A10,left(A1:A10,10)="substitute"))

enter image description here

| improve this answer | |
  • Thank you Gary! – Koen Oct 5 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.