SOLVED

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

Contributor

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

6 Replies

@Asparagus 

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

You can try this formula.

filter.JPG 

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?

best response confirmed by Asparagus (Contributor)
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.

 

@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 not 4 digits or not host.JPG

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

filter 3 words.JPG

 

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!
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!