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

 

[bebo021999]

Are cell C3 to C9 helpers?
Also, is C11 helper and do you want to hightlight cell C2?

 

[StephenCrump]

Just a quick look - I haven't tried to deconstruct your formula - but I'm guessing you're doing this?

	A	B	C	D	E	F
1
2		Blagh blah blah trumpet blah version piano blah gtr
3
4		trumpet	17
5		piano	38
6		gtr	49
7
8		Version	30
9
10			TRUE
Sheet1

Cell Formulas
Range	Formula
C4:C6	C4	=SEARCH(B4:B6,B2)
C8	C8	=SEARCH(B8,B2)
C10	C10	=MIN(SEARCH(B4:B6,B2))<SEARCH("Version",B2)
Dynamic array formulas.

 

[bebo021999]

Is UDF (User define formula using VBA) accepted?

 

[hnt007]

Are cell C3 to C9 helpers?
Also, is C11 helper and do you want to hightlight cell C2?

These are meant to show how I'm trying to come up with my final formula in C11. Yes, once I have this unique formula, I'll put it in Conditional Formatting for C2. Thanks!!

 

[hnt007]

Just a quick look - I haven't tried to deconstruct your formula - but I'm guessing you're doing this?

	A	B	C	D	E	F
1
2		Blagh blah blah trumpet blah version piano blah gtr
3
4		trumpet	17
5		piano	38
6		gtr	49
7
8		Version	30
9
10			TRUE
Sheet1

Cell Formulas
Range	Formula
C4:C6	C4	=SEARCH(B4:B6,B2)
C8	C8	=SEARCH(B8,B2)
C10	C10	=MIN(SEARCH(B4:B6,B2))<SEARCH("Version",B2)
Dynamic array formulas.

Thanks for helping! Well, I won't be able to write down instruments, I'll need formulas to extract them from the track descriptions that I will receive, I won't be able to use any cells in that XL to add formulas, so I need to find 1 formula that will do all the work and that I will put in Conditional Formatting.

 

[hnt007]

Is UDF (User define formula using VBA) accepted?

Unfortunately, can't use VBA. Can only end up with 1 formula that will only be placed in Conditional Formatting. Thanks!

 

[StephenCrump]

Just a quick look ...

I'll need formulas to extract them from the track descriptions ....

Yes, too quick, sorry.

I can do it like this:

	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,SEARCH("Version - no",B2),MIN(SEARCH(FILTERXML("<x><y>"&SUBSTITUTE(RIGHT(B2,LEN(B2)-s-12)," ","</y><y>")&"</y></x>","//y"),B2))<s)	text	NO

But if you're restricted to MacOS you won't have FILTERXML. Perhaps you do have the new TEXTSPLIT function, which we could use instead of FILTERXML?

 

[hnt007]

Yes, too quick, sorry.

I can do it like this:

	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,SEARCH("Version - no",B2),MIN(SEARCH(FILTERXML("<x><y>"&SUBSTITUTE(RIGHT(B2,LEN(B2)-s-12)," ","</y><y>")&"</y></x>","//y"),B2))<s)	text	NO

But if you're restricted to MacOS you won't have FILTERXML. Perhaps you do have the new TEXTSPLIT function, which we could use instead of FILTERXML?

WOW this looks perfect! But yes, I'm on macOS and I don't have this formula available to me yet...

 

[StephenCrump]

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.