How to join text in google sheets if it starts with ...?

Question

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))

Or

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

However this does not work.

Answer 1

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"))