Forum Discussion
Delete cells with exactly three syllables
I think this is what you're looking to do (the 3 consonants don't have to be consecutive):
=BYROW(words,LAMBDA(w,COUNTA(REGEXEXTRACT(w,"[^aeiou]",1,1))=3))Regexextract checks for anything not a vowel (case insensitive) and COUNTA is there to count the consonants. The function is applied with BYROW.
You could then filter for TRUE and delete as needed.
It's only after you comment did I realize the mistake I made:
I said syllable when I mean consonant. Very sorry!
OK, so now we're getting quite a bit closer to a clear (and thereby workable) definition. A question, do you actually want to delete the row in which the "offending cell" resides, or are you OK with blank cells where there once were words with three consonants?
If it's either of those, doing so would require a macro or VBA, and I personally work with neither method.
An alternative, if it's acceptable, is to create a "results column" into which you use a formula to copy the cells from the source column, with blanks for those cells that contained your three consonants.
I'll give some thought, time permitting, to how I'd write a formula to take out three consonant words -- but I'm quite sure that one or two others who've commented may be able to produce such a formula off the top of their heads. I'd like to learn, and this is an opportunity, but it'll take some time. You can help us all by clarifying along the lines of my questions in the first three paragraphs.
For now, I'm happy to have contributed to getting us a clearer definition of the task, a necessary first step on all occasions.
A results column is perfectly fine, as well as leaving the cells blank. I hope this answers your question.