Skip to main content
2 of 2
Post formatting; added Code Blocks for the formulas; removed fluff

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

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.

Koen
  • 3
  • 3