Forum Discussion

Brass Contributor

Oct 30, 2022

Solved

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

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 Col...

excel

SnowMan55
Oct 30, 2022
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.

Asparagus

Brass Contributor

Oct 30, 2022

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?

OliverScheurich

Gold Contributor

Oct 30, 2022

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.

Asparagus
Brass Contributor
Oct 30, 2022
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!