Simplifying a formula

[hnt007]

Hello and thanks for your help!

Is there a way to simplify this formula? =COUNTIF(C4,"*"&LEFT(C3,(FIND(" ",C3,1)-1))&"*")+COUNTIF(C4,"*"&MID(C3,FIND(" ",C3)+1,FIND(" ",C3,FIND(" ",C3)+1)-FIND(" ",C3))&"*")+COUNTIF(C4,"*"&TRIM(MID(SUBSTITUTE(C3," ",REPT(" ",100)),200,100))&"*")+COUNTIF(C4,"*"&TRIM(MID(SUBSTITUTE(C3, " ", REPT(" ", 999)), 2999, 999))&"*")+COUNTIF(C4,"*"&RIGHT(C3,LEN(C3)-FIND("~",SUBSTITUTE(C3," ","~",4)))&"*")>0

I need a smaller version of it to put in Conditional Formatting.

Track descriptions will all be different and I will never know what instruments are listed in advance, so that's why I'm using formulas to extract them. Here is the file Loading Google Sheets

 

[hnt007]

We could do the TEXTSPLIT part another way ....

	A	B
1
2		Blah blah blah trumpet blah version - no trumpet piano gtr
3
4		trumpet
5		piano
6		gtr
Sheet3

Cell Formulas
Range	Formula
B4:B6	B4	=LET(f,SEARCH("Version - no",B2),s,RIGHT(B2,LEN(B2)-f-12),m,MySplit(" "&s," ",99),MID(s,m,MySplit(s&" "," ",99)-m))
Dynamic array formulas.

MySplit: =LAMBDA(s,del,N,LET(MyDel,"@|^",f,FIND(MyDel,SUBSTITUTE(s,del,MyDel,SEQUENCE(N))),FILTER(f,ISNUMBER(f))))

This is just a first cut - it could be tidied up into the one LAMBDA. See if you can make it work?

I'll wait for the 14 new Text and Array functions.

Thanks for your help!

 

[StephenCrump]

It worked for you? You got it into one conditional formatting formula?

 

[hnt007]

It worked for you? You got it into one conditional formatting formula?

I will wait for the new formulas to be widely available, thanks again for helping me out!

 

[StephenCrump]

Soon, I hope. They do look good! Announcing New Text and Array Functions

 

[hnt007]

We could do the TEXTSPLIT part another way ....

	A	B
1
2		Blah blah blah trumpet blah version - no trumpet piano gtr
3
4		trumpet
5		piano
6		gtr
Sheet3

Cell Formulas
Range	Formula
B4:B6	B4	=LET(f,SEARCH("Version - no",B2),s,RIGHT(B2,LEN(B2)-f-12),m,MySplit(" "&s," ",99),MID(s,m,MySplit(s&" "," ",99)-m))
Dynamic array formulas.

MySplit: =LAMBDA(s,del,N,LET(MyDel,"@|^",f,FIND(MyDel,SUBSTITUTE(s,del,MyDel,SEQUENCE(N))),FILTER(f,ISNUMBER(f))))

This is just a first cut - it could be tidied up into the one LAMBDA. See if you can make it work?

I'll wait for the 14 new Text and Array functions.

Hi Stephen! The TextSplit function is now available on mac! YAY! How can I update your formula with it? Thank you so much

 

[StephenCrump]

The TextSplit function is now available on mac! YAY! How can I update your formula with it?

Like this perhaps?

	A	B
1
2		Blah blah blah trumpet blah version - no trumpet piano gtr
3		Blah blah blah blah version - no trumpet piano gtr
4		Blah blah blah piano blah blah version - no trumpet piano gtr
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B4	Expression	=LET(s,"version - no ",MIN(SEARCH(TEXTSPLIT(TEXTAFTER(B2,s)," "),B2))<SEARCH(s,B2))	text	NO

 

[hnt007]

Like this perhaps?

	A	B
1
2		Blah blah blah trumpet blah version - no trumpet piano gtr
3		Blah blah blah blah version - no trumpet piano gtr
4		Blah blah blah piano blah blah version - no trumpet piano gtr
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B4	Expression	=LET(s,"version - no ",MIN(SEARCH(TEXTSPLIT(TEXTAFTER(B2,s)," "),B2))<SEARCH(s,B2))	text	NO

WOW this is GENIUS! It works so well..thank you so much!!!