Forum Discussion
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...
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?
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.
- 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!