Excel Count Specific Words in Cell
December 15, 2020
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.
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!
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.
2. Excel A: In Excel, you can use a much longer formula, with LEN and SUBSTITUTE.
3. Excel B: I also tried the FILTERXML function, which was an interesting experiment!
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,
This can happen if Spell Check is ignoring words in all caps. To prevent that, change one of the spelling options:
Find more Excel data entry tips on my Contextures website.
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)
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)
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20201215ctx.html
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.
Last updated: April 4, 2021 9:02 PM