Forum Discussion
Delete cells with exactly three syllables
I'm not sure if this is too specific a command to use in Excel, but I have a column in which I want to delete all cells in that column with exactly 3 syllables in them. Is this even possible?
If you had been intending to identify word length in syllables, you would need a pronunciation source, such as most dictionaries. The Gutenberg Project offers (for free) this old-but-undated Webster's Unabridged Dictionary that could be used for that; the extraction of words and their syllable counts would include significant VBA coding. But the derived word forms (noun plurals, verb tenses, etc.) would be incomplete, and as this is an old work, some word spellings have changed, some pronunciations have changed, and other words were then not yet in use.
Identifying the number of consonants is more straightforward, but the sometimes-vowels Y and W cause a problem. See the attached workbook for more information and a partial solution. The count of consonants can mostly be done with formulas (I used helper columns). But to "delete" words requires a script; I include VBA code for that in the workbook. (The workbook is not macro-enabled; I will assume that you know how to create and edit macros.)
Edit: The forum software again loses a file that I attached (but it was not the one I intended anyway). Trying again...
Edit #2: Well that failed also. So access the workbook on OneDrive: 2025-10-02 RAHI words containing 3 consonants.xlsx
18 Replies
#MOHAMED
This is what it would take to solve in Excel.
- Pull together a list of the most common syllables found in English.
- Pull together a list of the most common words (or words most relevant to your data)
- Create all possible permutations (with repeats) from the list.
- Discard words which do not match words in your dictionary
- Discard words with precisely 3 syllables.
My advice is to use the best version of Copilot at: https://copilot.microsoft.com/
You can fit about 12,000 characters in a single chat post.
Only after my first comment did I realize the mistake I made in this question:
I said "Syllable" when what I should have said was "Consonant" I am so very sorry.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.
Question, if you don't mind: Is there a way to modify this to check for words with 3 consecutive consonants instead?
Forget about whether it's possible in Excel. Let me answer with a question about words: my question is whether it is possible to have a universally valid definition of what a syllable is, and how we would infallibly recognize one just based on looking at the word?
There are, after all, so many combinations of consonants and vowels that make up our words, along with multiple ways to pronounce them; I'm certainly aware that hyphenation in automated texts doesn't always follow a reliable algorithm. Yet that's what you seem to be expecting.
I had a PhD friend who acknowledged he'd pronounced "epitome" as a three syllable word--"e pi tome"--that last section rhyming with "home"
He was very embarrassed to learn it was properly "e pi to me"
How would you propose that Excel would recognize whether it was three syllables or four, absent a database containing a complete dictionary?
If you had been intending to identify word length in syllables, you would need a pronunciation source, such as most dictionaries. The Gutenberg Project offers (for free) this old-but-undated Webster's Unabridged Dictionary that could be used for that; the extraction of words and their syllable counts would include significant VBA coding. But the derived word forms (noun plurals, verb tenses, etc.) would be incomplete, and as this is an old work, some word spellings have changed, some pronunciations have changed, and other words were then not yet in use.
Identifying the number of consonants is more straightforward, but the sometimes-vowels Y and W cause a problem. See the attached workbook for more information and a partial solution. The count of consonants can mostly be done with formulas (I used helper columns). But to "delete" words requires a script; I include VBA code for that in the workbook. (The workbook is not macro-enabled; I will assume that you know how to create and edit macros.)
Edit: The forum software again loses a file that I attached (but it was not the one I intended anyway). Trying again...
Edit #2: Well that failed also. So access the workbook on OneDrive: 2025-10-02 RAHI words containing 3 consonants.xlsx
Thank you very much for this response!
Is there any way to modify this to then search for words with 3 consecutive consonants?
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.
