How do I get the FILTER & LEN formula to ignore cells that has a specific text in it.

[Asparagus]

I created a script in excel in which the host's monologs (among other things) are written in Column D. Above each monolog, "HOST" is written as an indicator. I was hoping to extract every note in Column D except "HOST" while using the FILTER/LEN Formula.

Is this possible

[#2]

@Asparagus 

=FILTER(D2:D9,LEN(D2:D9)<>4)

You can try this formula.

 

[#3]

Thank you. This works great for this script because none of the other cells in column D had 4 characters. I can see other great purposes for this formula too!
But if there were other 4 letter words or if by chance I wanted to expend upon this, let's say it was a three word phrase like "Refer to Blocking" that I did not want to extract, yet there were other phrases that had the same character count... how would I differentiate the undesired phrase from the rest?

Also is there a way for it to ignore empty cells?

[#4 · Accepted solution]

@Asparagus To answer both your questions:

=FILTER( D2:D9, (D2:D9<>"host") * (D2:D9<>"Refer to Blocking") * (D2:D9<>"") )

The multiplication is effectively the condition AND.

[#5]

@Asparagus 

=FILTER(D2:D13,((LEN(D2:D13)<>4)+(D2:D13<>"host"))*(D2:D13<>"")))

You are welcome. You can try this formula to return all cells that don't have a length of 4 digits or don't contain "host" and ignore blanks.

=FILTER(D2:D13,(LEN(TRIM(D2:D13))-LEN(SUBSTITUTE(D2:D13," ",""))+1=3)*(D2:D13<>"Refer to Blocking"))

You can try this formula to return all cells that have 3 words besides "Refer to Blocking". Blanks are ignored because the formula looks for cells with 3 words.

[#6]

This works great too but though the chances are low, I would have to be careful that none of the other cell falls under the same criteria causing it not to be extracted.

Still I can see how the purpose of this formula will benefit me in other worksheets. Thanks so much!

[#7]

This worked perfectly! I didn't mean for my request to have "host" & "Refer to Blocking" ignored at the same time during the extraction, so it is a happy bonus to know that it possible, any amount for that matter. Awesome, thanks so much!