Contextures News 2020-12-15

Excel Count Specific Words in Cell

December 15, 2020

Spell check problems, count specific words, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Thank you for reading the news, and you'll get the next email after the holidays, on January 12th. We've all earned a break after getting through 2020, right? Happy holidays to you and your family, and wishing you a great start in 2021!

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Count Words

Recently, someone asked for help with a formula to count specific words within a single cell. For example, "air" found, on its own, 2 times in cell B7. We don't count it as part of "hair".

What formula would you use to count those words? It's easy in Google Sheets, but not in Excel!

count specific text items in one cell


NOTE: Go to the Count Specific Text in Cell page, to see all the formulas, and details on how they work. There's also a sample file that you can download.

1. Google Sheets: SPLIT formula removes spaces, splits at commas, then counts matching words.

  • =COUNTIF(SPLIT(SUBSTITUTE(B5," ",""),","),$B$2)

2. Excel A: In Excel, you can use a much longer formula, with LEN and SUBSTITUTE.

  • =(LEN("|" & SUBSTITUTE(SUBSTITUTE(B5," ",""), ",","," & "|") & ",")
    -LEN(SUBSTITUTE("|" & SUBSTITUTE(SUBSTITUTE(B5," ",""),",","," & "|") & ",","|" &$B$2&",",""))) / LEN("|" &$B$2&",")

3. Excel B: I also tried the FILTERXML function, which was an interesting experiment!

Spell Check Problem

Do you ever spell check your work in Excel? On the Excel Ribbon, click the Review tab, then click Spelling, at the far left. If you're lucky, you'll see a message, "Spell check complete. You're good to go!"

If there are spelling errors, the Spelling dialog box opens, to help you correct them. But sometimes, there's an error on the worksheet, and Spell Check doesn't catch it! For example,

  • Excel found the misspelling in cell A5.
  • Why didn't it catch the same misspelling in cell A2?

spelling error in Excel

This can happen if Spell Check is ignoring words in all caps. To prevent that, change one of the spelling options:

  • On the Excel Ribbon, click File, then click Options
  • At the left, click Proofing
  • Remove the check mark for "Ignore words in UPPERCASE", then click OK

Find more Excel data entry tips on my Contextures website.

Excel Articles

Here are a couple of Excel-related articles that you might find useful or interesting.

Excel VBA: Get ready for the new year with new skills. On Coursera, take the free course, Excel/VBA for Creative Problem Solving, Part 1. It's taught by Charlie Nuttelman, a highly-rated instructor from University of Colorado Boulder. (Level - Int/Adv)

New Function - With Excel's new LAMBDA function, you won't need VBA to create custom functions in your workbooks. This function is in the latest beta (Insider) version of Excel for Office 365. (Level - Int/Adv)

Also see: My Excel Products || Previous Issues

Zoom Christmas

This year, we'll have a Zoom Christmas, celebrating with our family online, instead of in person. Lately, we've been watching the Taskmaster game show on YouTube, so I've sent the family a couple of challenges for this year's Christmas Eve games: 1) Build a pandemic snowman, 2) Make a Christmas hat

Happy holidays, and I'll see you back here on January 15th!

(Gingerbread house made by my daughter and grandson)

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser:
I'll also post any article updates or corrections there.

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info


Last updated: January 8, 2021 12:33 PM