Skip to main content
1 of 2
Koen
  • 3
  • 3

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 tought it would be =IF(F9:AE9="^substitute";JOIN(" ";F9:AE9)). Or =JOIN(" ";F9:AE9;IF(F9:AE9="^Substitute")). However this does not work. Could someone help me out?

Greetings Koen

PS: I'm not an advanced user so baby steps would be great :)

Koen
  • 3
  • 3